Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Rolling total needs changes by filter too

I have a problem which is driving me insane and as many times as I have tried I just can't get it to work.
So firstly I have two measures:
Measure 1 sums the number of ITPReviewed by area which works very well
1. AreaCumulative_ITP = SUMX(FILTER(ITP_InfoTable,
RELATED(Areas[Area]) <>""),
ITP_InfoTable[ITP Reviewed])

Measure 2 sums up the same things but based on the period
2. PeriodCumulative_ITP = CALCULATE(SUM(ITP_InfoTable[ITP Reviewed]),
FILTER( ALLSELECTED(ITP_InfoTable), ITP_InfoTable[Period] <= MAX(ITP_InfoTable[Period]) ) )

Now when I have the two in the table in Power BI (below)

Now when I filter by Period

I have the area and the Period in synch.

Now what I want to happen is when I remove January from the search/filter
I still need the Period Cummulative to keep the total values from previously, so the value in the first line should be 50 13+37 not 24 

Any ideas on how I can achieve this mini cumulative value

Thanks

 

Greg

 

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , In all such case, have a period table or date table as a separate table and then you can use all in place of allselected.

    In case you do not have date  you can create one based on your format

    Date = "01-" & [Month] &"-" &[Year] // change data type as date

     

    In case you only need year wise use YTD

    example

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[date])))

    or

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=max(date[date])))

     

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.