Cumulative Total prior year Matrix Total

AliB · May 21, 2019, 2:25pm

Hi

I’m using this formula to calculate cumulative totals for prior period

Cumulative Transactions LY = 
VAR LastTransactionDate = CALCULATE( LASTDATE( Transactions[Create Date]),ALL(Transactions))

RETURN
IF( SELECTEDVALUE( Dates[Date] ) > LastTransactionDate,BLANK(),
     CALCULATE( [Total Transaction LY],
            FILTER( ALLSELECTED(Dates),
                Dates[Date] <= MAX(Dates[Date]))))

The filters on the report are FY and Qtr

I want to be able to compare Current Period with the SAME period Last year in a card

However even though the formula appears to calculate correctly… the table stops at the last transaction date and gives a cumulative total LY of £7.5M the total says £10.4M which is incorrect as this is the total for the whole of Qtr 1 and Qtr 2 for the previous year.

I have tried the following:

Cumulative Transactions LY with Total = 
VAR LastTransactionDate = CALCULATE( LASTDATE( Transactions[Create Date]),ALL(Transactions))
VAR CumulativeTransLY = IF( SELECTEDVALUE( Dates[Date] ) > LastTransactionDate,BLANK(),
     CALCULATE( [Total Transaction LY],
            FILTER( ALLSELECTED(Dates),
                Dates[Date] <= MAX(Dates[Date]))))
VAR TransactionLYTotal = SUMMARIZE( Dates,Dates[Date], "CumTransLY", CumulativeTransLY )

RETURN
IF( HASONEVALUE( Dates[Date] ) ,
    CumulativeTransLY,
        SUMX( TransactionLYTotal, [CumTransLY]))

but this returns another number

How can I get the card to show £7.5M? I assume if I fix the total of the table the card should also be correct but having watched the videos and trying to implement - its not happening

Help please

Jawed · May 21, 2019, 8:53pm

Hi there,

Why are you not calculating TYTD or Cumulative for this year and then use calculate and sameperiodlastyear?

AliB · May 21, 2019, 9:21pm

I am but the table total is not correct

sam.mckay · May 23, 2019, 5:39am

You don’t really have to use the cumulative total here because it’s in the card and you just want to total.

So SALES LY should be fine to use.

But you’ll want to modify it.

Something like this is likely all you need.

Sales LY To Current Date = 
VAR LastTransactionDate = CALCULATE( MAX( Sales[OrderDate] ), ALL( Dates ) )

RETURN
IF( HASONEVALUE( Dates[Date] ),
    IF( SELECTEDVALUE( Dates[Date] ) <= LastTransactionDate, [Sales LY], BLANK() ),
        SUMX( 
            FILTER( ALLSELECTED( Dates[Date] ), Dates[Date] < LastTransactionDate ),
                [Sales LY] ) )