## Service

Highlighted
Regular Visitor
Posts: 25
Registered: ‎01-24-2018

# 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

Accepted Solutions
Community Support Team
Posts: 1,181
Registered: ‎02-06-2018

## Re: dax for cumulative total for last 3months

Hi

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_))```

Regards,

Jimmy Tao

Regards,

Jimmy Tao

All Replies
Super User
Posts: 8,191
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.

Proud to be a Datanaut!

Community Support Team
Posts: 1,181
Registered: ‎02-06-2018

## Re: dax for cumulative total for last 3months

Hi

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_))```

Regards,

Jimmy Tao

Regards,

Jimmy Tao