Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
banupriya45
Frequent Visitor

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

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.

7 REPLIES 7
daxer-almighty
Solution Sage
Solution Sage

 

[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
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

 

 

 

 

 

 

 

 

20210710_083150.jpg20210710_083131.jpg20210710_083242.jpg

 

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 screenshot20210710_090846.jpg

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.

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?

 

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:

 

daxeralmighty_0-1626250402814.png

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.