Cumulative total sum for last 12 months from selected year and month

[banupriya45]

Hi,

Can you help me with dax formula.

I want to display Cumulative total of last 12 months from selected month and year.

Slicer - Year and Month I am taking from day dim table.

In the table view sales amount from fact table then year-month from different calender table.

Problem : It is not coming with running total.

Thanks in Advance.

[daxer-almighty]

[12M Total] =
var LastVisibleDate = MAX( Days[Date] )
var PeriodToSumOver =
    DATESINPERIOD(
        Days[Date],
        LastVisibleDate,
        -12,
        MONTH
    )    
var _12MonthTotal =
    CALCULATE(
        [Total],
        PeriodToSumOver,
        // If your Days table is marked
        // as a date table, you don't
        // have to put REMOVEFILTERS
        // here.
        REMOVEFILTERS( Days )
    )
return
    _12MonthTotal

Be aware that if you select a period which ends before the first year in your Days table, you won't be getting a 12M total but the total from the beginning of you calendar up to the last day visible. If you want to make sure that you don't return anything for such periods, you have to make sure that the last visible day is not in the first year of your calendar.

[amitchandak]

@banupriya45 , if only need 12 month total

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

If you need cumulative and also want to display 12 months you need an independent date table

Date and calendar both are date tables. The calendar table does not have a join with fact. The date is joined. In filter/slicer you need to use calendar table

Cumm Sales =
VAR _min = MINX( allselected('Calendar') , 'Calendar'[Date] )
VAR _max = MAXX(allselected('Calendar') , 'Calendar'[Date] )
return
CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date]) && date[Date] >=_min && date[Date] <=Max ))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[banupriya45]

Hi @amitchandak ,

I have attached a data model screenshot and report screenshot for your reference.

Year and month are taken from the Date table. In the table view, Month-year drag and dropped from Calendar table then Running from Event_PR table. Based on month and year slicer selection from date table, Calendar is displaying last 12 months but Running total is not happening. Please help

[banupriya45]

If I use below DAX , the running total is computing properly but month and year display additionally.

Example: If i select year=2021, month= Feb then in table view it displays 2020 feb to 2021 feb with correct computing and
also computing until max calendar date

Running =
VAR EndMonth = MAX ('Calendar'[Date])
VAR StartMonth = FIRSTDATE(DATEADD('DATE'[Date],-12,MONTH))
RETURN
CALCULATE ([Total],REMOVEFILTERS('DATE'),FILTER(ALL('Calendar'),
'Calendar'[Date]>=StartMonth &&
'Calendar'[Date]<=EndMonth))

Attached the screenshot

[daxer-almighty]

Here's a full solution to your problem and totally dynamic:

https://1drv.ms/u/s!ApyQEauTSLtOgZZ3D4EXXR6TgKgbpQ?e=WqxPWK

The only thing you have to change in the code is the number of months you want to see. I've set it to 6 because it was easier for me to test this model. Just change it to 12. The place to do it is in the code of the Anchor Month calculated table.

[banupriya45]

Hi @daxer-almighty ,

The solution is working if I use calendar table columns in the slicer and Day table columns in the table view. Why I not able to use Day table columns in the slicer and calendar table columns in table view?

[daxer-almighty]

I don't quite get what you're talking about since my solution has Anchor Month and Dates tables but this is working the way it does because of this---a relationship with many-to-many cardinality and one-way filtering:

and there's no way for it to work in a different way. One table must filter the other but not the other way round. The one with lower granularity must filter the one with higher granularity and the filter must go one-way. IMHO you should learn more about how PBI, models and DAX work (try the free YT courses, some of them are really valuable and led by true experts). There would be too much for me to say about why this is the correct way to do it.