Forum Discussion

browna6's avatar
browna6
Frequent Visitor
3 years ago

Calculating 3 months in from MAX date

Hi

 

I have the following calculation suming up 3 months from my max date in the table - which is working correctly

 

Max 3 months sum =
CALCULATE (Table[Market Value €],DATESINPERIOD('Dates'[Month],MAX('Dates'[Month]),-3,MONTH))

 

However, how can i then sum up the previous 3 months of that from my max date. So i want to calculate from -6 months in from the Max month in my date's measure to -3 in 

 

Thank you in advanced 

Need Help
Tips and Tricks

3 Replies

  • croberts21's avatar
    croberts21
    Icon for Continued Contributor rankContinued Contributor
    3 years ago

    Can you give us the max date you will use, then the resulting begin and end dates? 

     

    So if the max date is June 1, 2022 then we want to sum March 1, 2022 through June 1 2022? Is that right?

    • browna6's avatar
      browna6
      Frequent Visitor
      3 years ago

      Hi. So if the Max date was Dec 2022 - i would like to show the sum of July 2022, Aug 2022 & September 2022. Thanks 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi browna6 ,

         

        try this:

        Measure = 
VAR _max_date = MAX('Dates'[Date])
VAR _start_date = EOMONTH(_max_date,-6)+1
VAR _end_date = EOMONTH(_max_date,-3)
VAR _totals = CALCULATE(SUM('Table'[Market Value €]),'Dates'[Date]>=_start_date&&'Dates'[Date]<=_end_date)
RETURN
_totals

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum