Forum Discussion
Sum Total is Incorrect When Comparing Two Fiscal Years When One is Not Over
I am trying to create a table where I am subtracting the current fiscal year totals by month by the same period last year. We have a lag on our data and are viewing data a month or two past when it occured. Below is exactly what I want the chart to look like except for the total is incorrect. I know it is because it is treating April, May, and June as $0 for this current fiscal year minus the receipts from last fiscal year. But I cannot figure out how to fix it. I am still very new to PowerBI so I still have a lot to learn.
Difference =
VAR ReceiptsPriorYear =
CALCULATE(
sum(Receipts[Net Amount]),
PARALLELPERIOD(
'Calendar'[Date],
-12,MONTH))
VAR ReceiptsCurrentYear =
CALCULATE(
Sum(Receipts[Net Amount]))
RETURN
if((sum(Receipts[Net Amount])) <> 0,
CALCULATE(
(ReceiptsCurrentYear - ReceiptsPriorYear), DATESYTD('Calendar'[Date], "30/6")),"")
4 Replies
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/n4TYhF2ARe8I 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.
