Forum Discussion
Previous Year comparison - account for partial current Year
Hi,
Please can I have assistance when trying to write DAX comparing to the prior year?
When Month is in scope, and for full prior years, the calculation returns the appriopriate amount for "Previous Year (£)"
However, for 2024, it returns the full amount for 2023 and I don't know how to write the logic following the RETURN in DAX to subsitute with the "YTD Cost Previous (£)" .
You can see the "Previous Year Comp (%)" performs as expected, I just can't get the right number to populate in the table. I'm toying with a column that can check if the Month# is before the latest "Invoice Date" (and this calc is called "Relevant")
Thanks very much for your help!
Please find the workbook here: Year Comparison Question
Hi ChrisCoxhill ， amitchandak thank you for your prompt reply!
Please try the following measure to show only the month totals for the same period last year:Previous Year 2 = CALCULATE( SUM('SW Energy'[Cost]), FILTER( SAMEPERIODLASTYEAR('Date'[Date]), MONTH('Date'[Date]) <= MONTH(MAX('SW Energy'[Invoice Date])) ) )Result:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
ChrisCoxhill , Based on what I got, Try like
YTD Cost Previous (£) = VAR CostYTD = CALCULATE(SUM('SW Energy'[Cost]),CALCULATETABLE(DATESYTD('Date'[Date]),'Date'[Relevant])) VAR CostPreviousYTD = if(ISINSCOPE('Date'[Month]), CALCULATE(SUM('SW Energy'[Cost]),CALCULATETABLE(DATESYTD(dateadd('Date'[Date],-1,YEAR)),'Date'[Relevant])),CALCULATE(SUM('SW Energy'[Cost]),DATESYTD(dateadd('Date'[Date],-1,YEAR)))) RETURN CostPreviousYTDHi, thank you for your reply!
I am sorry to ask you to tweak a calc please:
Previous Year Comp (%) = VAR CurrentCost = SUM('SW Energy'[Cost]) VAR CostPreviousYTD = CALCULATE(SUM('SW Energy'[Cost]),CALCULATETABLE(SAMEPERIODLASTYEAR('Date'[Date])),'Date'[Date]<=MAX('SW Energy'[Invoice Date])) RETURN DIVIDE(CurrentCost-CostPreviousYTD,CostPreviousYTD)This may explain myself better and it shows up like this now:
You can see your column added at the end.
Everything with the ticks and crosses now works fine, just the Previous Year 2024, as we don't have a full year yet I just want to compare it to the YTD from 2023 as circled; £334. The answer should not be -59.6%, it should be +6.0% as per the first screenshot, but then that compromises the other full years comparisons 😞
I don't know how to do this sorry and I greatly appreciate your assistance thank you!
Hi amitchandak sorry I think I have to "mention" you. After reviewing the logic of your calc, you are evaluating what to do when Month is in scope vs when not (Year), if I've understood correctly.
I hope you can see now what I'm trying to do is JUST for 2024 (the latest year) - when Year IN SCOPE, the comparison amount shouldn't be the full amount, it should be based on YTD
Again, thanks a lot for any assistance!Hi ChrisCoxhill ， amitchandak thank you for your prompt reply!
Please try the following measure to show only the month totals for the same period last year:Previous Year 2 = CALCULATE( SUM('SW Energy'[Cost]), FILTER( SAMEPERIODLASTYEAR('Date'[Date]), MONTH('Date'[Date]) <= MONTH(MAX('SW Energy'[Invoice Date])) ) )Result:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thak you so much for this! That seems to have nailed it after implementing your solution and testing.
I appreciate the style of your reply too, can very clearly see it working side by side with the "wrong" version.Cheers Joyce!
Chris
