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 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)))
DAX Calculation
Help with DAX Measure

8 Replies

    • 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

    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

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

    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