Help with sum between two dates

DAX DAX Calculations
1

I am having issues getting the right dax measure to find the cummulative total between two dates in the past. the tricky part is i need to go back a 6mth period and then get the total of the previous 6 months.

So to explain in example, in my file April 2020 would be the sum for Oct 2019 (6 mths ago) thru May 2019 (12 mths ago).

I am using a date table. I want to take selected month and get the sum for the previous 6-12 mths. I tried the following but its just not working.

TEMP =
VAR Sixmths = DATEADD(‘Calendar’[Date],-6,MONTH)
VAR Elevenmths = DATEADD(‘Calendar’[Date],-11,MONTH)
RETURN
CALCULATE(
SUM(‘Sioux Falls’[Gross Charges]),
DATESBETWEEN(‘Calendar’[Date],Sixmths,Elevenmths))

In the attached spreadsheet you can see for April 2020 = SUM(May19 thru Oct19)charges.xlsx (9.5 KB)

really up against time crunch so any help is VERY much appreciated.

2

Hi @cwnoll,

Welcome to the forum!

There are many different ways to approach this. You’re looking for a cumulative pattern on a monthly granularity with an offset to the current month over a fixed period.

The Date table in the sample includes a MonthOffset, you can find a link to that here

Cum Sales prev 6-12 months = 
VAR ThisMonth = SELECTEDVALUE( Dates[MonthOffset], LOOKUPVALUE( Dates[MonthOffset], Dates[Date], TODAY()))
RETURN

CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[MonthOffset] >= ThisMonth -11 &&
        Dates[MonthOffset] <= ThisMonth -6
    )
)

image
image730×310 8.55 KB

Here’s my sample file. I hope this is helpful.
Cumulative value.pbix (138.4 KB)

3 Likes
3

@Melissa,

:clap:. Terrific example of how the offset method really simplifies the approach to these sorts of problems.

  • Brian
4

Hi @cwnoll, Welcome to the Forum! A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

5

amazing example, and thank you so much for the effort and help.