Current Year YTD
IF DATEPART('year', [Reported Date]) = DATEPART('year', [Today's Date])
AND [Reported Date] <= [Today's Date]
THEN {FIXED :COUNT([Claim ID])}
END
Previous Year YTD
IF DATEPART('year', [Reported Date] )= DATEPART('year', [Today's Date])-1
AND [Reported Date] <= DATEADD('year', -1, [Today's Date])
THEN { FIXED : COUNT([Claim ID])}
END
Absolute Difference
COUNT([Current Year YTD]) - COUNT([ Previous Year YTD])
Percentage of Difference
(COUNT([Current Year YTD]) - COUNT([ Previous Year YTD])) / COUNT([ Previous Year YTD])
But im not getting the Absolute difference and percentage of diff when we drag these calculation.
i have added the below with static value, but i need to get it updated dynamically by changing the date of year filter,
Im pasting the TWBX files too, can you guys pls help me out this.
Hi @vinoth s, you asked me to take a look at this after a chat on DMs. I like what Hari and Jim have suggested so far! First up here is what I noted in the chat in case it helps others, after that I'll comment further:
First thing I spot is that your approach so far is geared towards the current and previous year using TODAY(). To use a *selected year* and *it's* previous year to that you've got a few options:
(1) Use a parameter instead of a filter. The filter is filtering the data down to the selected year only, so when you then try to say "previous year", that previous year isn't in the data set. With a parameter you can make it populate based on the years in the data set. And you can write a calc that says
SUM( IF YEAR([Reported Date]) = [Selected Year] THEN Blah END ), and
SUM( IF YEAR([Reported Date]) = [Selected Year]-1 THEN Blah END )
(2) If you need multi-select then consider using set controls instead (Google Tableau Set Controls Flerlag to find a great blog series!)
(3) Filter on a table calc and use table calcs for the change / % difference. Because a table calc filter happens after things like LOOKUP, you can lookup the previous years value. You need YEAR in the view, but then hide it. Then use a calc LOOKUP(MAX(YEAR([Reported Date])),0) as the filter. Now you can do LOOKUP(SUM(Blah),-1) to get the prior years value.
I've also since had a look at the workbook you sent me, to illustrate my points above:
Here I have 2025 only selected. So 2024 is filtered out of the data. But your calculation is relying on 2024 (Year of TODAY() minus 1) being IN the data. So count for previous year is 0, and your percentage difference is hence wrong too.
If I select 2024 and 2025 the calculations can work because year of TODAY and year of TODAY minus are in the data:
If I work further back through your years you can see how the data if filtered by your filter, but the calculations are not because they are hardcoded to use TODAY
The options I've outlined above, or that Jim mentions are where you need to go next. E.g. if I change over to a parameter:
Note: I'm just showing the change to Previous Year YTD here, you do a similar thing in Current Year YTD (using "Selected Year" instead of YEAR(TODAY)).
Now when you change the selected year parameter the calcs will work on the right years:
Now, I haven't changed the {FIXED...} part of your calcs here but they look a bit strange to me and I wonder if you will need a combination of what I've suggested and what Hari has suggested.
If parameters won't work for you you can try changing over to using table calcs. Filter on LOOKUP(MAX(YEAR([Reported Date])),0) and then you can then have YEAR on your view, hide that, and use LOOKUP(<your aggregated measure>,-1) to get last years value.
Workbook with the parameter idea attached in case it helps.
Ta, Steve
