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
mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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'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?

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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

  • 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