Reply
Highlighted
Frequent Visitor
Posts: 12
Registered: ‎01-24-2018
Accepted Solution

dax for cumulative total for last 3months

Hi,

I have a tabular model where i have to create a measure to calculate Running total for last 3 months.The expected output has a grain to be per site per month.The issue is: fact table and month dimension are joined using Report Month field and date dimension has Report Month as FK.This fact table does not have day level data.

How do i acheive this?Attached a sample data

rolling sum.PNG

 

 

 


Accepted Solutions
Community Support Team
Posts: 701
Registered: ‎02-06-2018

Re: dax for cumulative total for last 3months

Hi pBI1234,

 

To achieve your requirement, you can create a measure and use DAX like this:

Measure = 
VAR from_ = EOMONTH(MAX(Table1[ReportMo]), -4)
VAR end_  = MAX(Table1[ReportMo])
RETURN
CALCULATE(SUM(Table1[Scrap]), FILTER(ALLEXCEPT(Table1, Table1[Site]), Table1[ReportMo] > from_ && Table1[ReportMo] <= end_))

1.PNG  

PBIX here: https://www.dropbox.com/s/rbo2z3vemawcuvf/dax%20for%20cumulative%20total%20for%20last%203months.pbix...

 

Regards,

Jimmy Tao

Regards,

Jimmy Tao

View solution in original post


All Replies
Super User
Posts: 7,619
Registered: ‎07-11-2015

Re: dax for cumulative total for last 3months

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490, particularly the part about not posting images of data but rather text that can be copied.

 

But, in general you need to use an ALL or ALLEXCEPT in your measure calculation to override the relationship context.









Did I answer your question? Mark my post as a solution!



Proud to be a Datanaut!










Community Support Team
Posts: 701
Registered: ‎02-06-2018

Re: dax for cumulative total for last 3months

Hi pBI1234,

 

To achieve your requirement, you can create a measure and use DAX like this:

Measure = 
VAR from_ = EOMONTH(MAX(Table1[ReportMo]), -4)
VAR end_  = MAX(Table1[ReportMo])
RETURN
CALCULATE(SUM(Table1[Scrap]), FILTER(ALLEXCEPT(Table1, Table1[Site]), Table1[ReportMo] > from_ && Table1[ReportMo] <= end_))

1.PNG  

PBIX here: https://www.dropbox.com/s/rbo2z3vemawcuvf/dax%20for%20cumulative%20total%20for%20last%203months.pbix...

 

Regards,

Jimmy Tao

Regards,

Jimmy Tao