Forum Discussion

cwnoll's avatar
cwnoll
Frequent Visitor
6 years ago

help with getting total between two dates

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...
DAX Calculation
Help with DAX Measure
cwnoll's avatar
cwnoll
Frequent Visitor
6 years ago

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's avatar
vivran22
Community Champion
6 years ago

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