Forum Discussion
Sum Total is Incorrect When Comparing Two Fiscal Years When One is Not Over
ThisIsHalloween First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
I gave this a shot but it is still looking the same:
Difference = VAR ReceiptsPriorYear = CALCULATE( sum(Receipts[Net Amount]), PARALLELPERIOD( 'Calendar'[Date], -12,MONTH)) VAR ReceiptsCurrentYear = CALCULATE( Sum(Receipts[Net Amount])) RETURN if(HASONEFILTER('Calendar'[FY]),IF(sum(Receipts[Net Amount]) <> 0, CALCULATE( (ReceiptsCurrentYear - ReceiptsPriorYear), DATESYTD('Calendar'[Date], "30/6")),""), SUMX( (filter (Receipts, max('calendar'[fy]))),ReceiptsCurrentYear))ThisIsHalloween You didn't implement this correctly. ReceiptsCurrentYear is a variable and variables are static so I'm not sure what exactly you are summing there in that last SUMX but it's going to be the same number just repeated over and over and over which I can't imagine is correct. The solution presented shows to get a measure that calculates correctly at the individual row level and then create a second measure that returns that measure at the row level and a SUMX of the measure at the total level.
I tried again and the totals are still not correct. Where am I messing up the hasonefilter formula?
Difference = VAR RPY = CALCULATE( sum(Receipts[Net Amount]), PARALLELPERIOD('Date'[Date],-12,month)) VAR RCY = CALCULATE( SUM(Receipts[Net Amount])) RETURN if(HASONEFILTER('Date'[FY]), if(sum(Receipts[Net Amount]) <> 0, CALCULATE( (RCY - RPY), DATESYTD('Date'[Date], "30/6")), "0"))
