help with getting total between two dates

[cwnoll]

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, the max month is April 2020. I need to get 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.

6-6 Gross Charges =

CALCULATE(

SUM('ProjectX'[Gross Charges]),

DATESBETWEEN(

'Calendar'[Date],

DATEADD('Calendar'[Date],-6,MONTH),

DATEADD('Calendar'[Date],-11,MONTH)))

[vivran22]

@cwnoll 

Did any of the replies answers your query? If it does, then please mark it as a solution.

If your problem is still unresolved, please share more details.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Anonymous]

No wonder it doesn't work... If you closely look at the definition of DATEADD (as you should do with all functions the functionality of which you don't fully know), you'll notice that it returns A TABLE, not a value. But DATESBETWEEN expects single values as the 2nd and 3rd argument.

dax.guide/datesbetween/
dax.guide/dateadd/

So, if you get the right dates and then stick them into DATESBETWEEN, you'll get a correct answer.

Best
D

[mahoneypat]

Please try this approach instead.  I've never actually tried this, but it just might work.  I thought it might unintentionally include the mindate in the date range, so I filtered that out.  Please let me know if it works.  If not, there is always the brute force approach using DATE(Year( ), Month(), Day(), but that is less elegant.

6-6 Gross Charges =
VAR mindate =
MIN ( 'Calendar'[Date] )
VAR prev12mos =
DATESINPERIOD ( 'Calendar'[Date], mindate, -12, MONTH )
VAR prev6mos =
DATESINPERIOD ( 'Calendar'[Date], mindate, -6, MONTH )
VAR prevprev6mos =
FILTER ( EXCEPT ( prev12mos, prev6mos ), 'Calendar'[Date] <> mindate )
RETURN
CALCULATE ( SUM ( 'ProjectX'[Gross Charges] ), prevprev6mos )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[vivran22]

Hello @cwnoll 

You may try the following link:

https://www.vivran.in/post/moving-average-using-dax

In this replace the average calculation with the sum.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[mahoneypat]

Vivek,

I tried out this measure and had to make one a small correction (removed Filter() part and adding "-1" to each mindate).  Please see the updated measure below.

6-6 Gross Charges =
VAR mindate =
MIN ( 'Calendar'[Date] )
VAR prev12mos =
DATESINPERIOD ( 'Calendar'[Date], mindate-1, -12, MONTH )
VAR prev6mos =
DATESINPERIOD ( 'Calendar'[Date], mindate-1, -6, MONTH )
VAR prevprev6mos =
EXCEPT ( prev12mos, prev6mos )
RETURN
CALCULATE ( SUM ( 'ProjectX'[Gross Charges] ), prevprev6mos )

Also, don't worry about trying to use functions and do things you don't fully understand.  Almost everyone in this community is sharing knowledge from all the mistakes they've made.  Keep learning and trying new things.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[cwnoll]

Thank you so much for the quick feedback, but this still didnt work. Maybe I can attempt to explain better. 

My current month is April 2020, I need to find the SUM of the gross charges for May 2019 thru Oct 2019. The idea is April 2020 would skip previous 6 months, then get the sum of the previous 6 months to that.

Since current Month is April 2020, it would skip Mar2020,Feb2020,Jan2020,Dec2019,Nov2019,Sep2019, then sum Oct2019-May2019.

Does this help at all?

[vivran22]

@cwnoll 

Try this:

Previous Six Month =
VAR _CurrentMonth =
    MAX ( dtCalendar[Date] )
VAR _StartMonth =
    EDATE ( [CurrentMonth], -12 ) + 1
VAR _Filter =
    DATESINPERIOD ( dtCalendar[Date], _StartMonth, 6, MONTH )
VAR _Sum =
    IF (
        _StartMonth >= FIRSTDATE ( ALLSELECTED ( dtCalendar[Date] ) ),
        CALCULATE ( SUMX ( dtCalendar, [Total Sales] ), _Filter )
    )
RETURN
    _Sum

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[mahoneypat]

What result did you see with my latest expression?  I was pretty sure that would do it.  Are you using the Month column from your Calendar table in your visual?  Can you say more about your model?

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat