Forum Discussion

ThisIsHalloween's avatar
ThisIsHalloween
Helper I
2 years ago

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 o...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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.

ThisIsHalloween's avatar
ThisIsHalloween
Helper I
2 years ago

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"))