Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Getting Prevous Month Value Based on Max Date

Hi there,   I would like to create a measure that will pick Previous Month's Value based on Max Date in my data set. and Max date always should be latest date which is also end of a month. ex: In ...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    You may create formula like DAX below.

     

     

    Column: Max Date = IF(MAX('My Place Data'[Reporting Date])=EOMONTH(TODAY(),-1),EOMONTH('My Place Data'[Reporting Date],-1),EOMONTH(TODAY(),-1))
 
Measure: PM_Value = CALCULATE(SUM('My Place Data'[Values]),FILTER('My Place Data','My Place Data'[Reporting Date]=MAX('My Place Data'[Max Date])))

     

     

    Result:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k's avatar
parry2k
Super User
6 years ago

Anonymous can you please explain, how your raw data looks like and what you are trying to achieve. In your post you mentioned my dataset looks like this, but your screens shot showing dataset1 and dataset2, not sure which dataset you are referring too.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi parry2k thank you for looking at my question. My data set will be dynamic, It will always have historical data plus a new reporting date depending on when I am refreshing the data. so let's say I will do a refresh next Monday my data set will be like:

 

 

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous got that, so now what? What you want to achieve?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k I want to write a measure that will give me the value for second max date in my data set ignoring if I have any date that is not end of the month.

      so for both tables Max date is 30/11/2019 and second max date is 31/10/2019. The the value at second latest date is 300 (the value that I want to get.)

       

      I wrote following dax measure to get the max date of my dataset:

      Max Date = CALCULATE(VALUES('My Place Data'[Reporting Date]),FILTER('My Place Data',OR(EOMONTH(MAX('My Place Data'[Reporting Date]),0)=TODAY(),'My Place Data'[Reporting Date]=EOMONTH(TODAY(),-1))))

       

      and following one to get the value for Max second date

      PM_Value = CALCULATE(OPENINGBALANCEMONTH([Values],'Calendar'[Date]),FILTER('Calendar','Calendar'[Date]=[Max Date])))
       
      Those formulas are working for dataset1 but for dataset2 dax gives me the value for 30/09/2019 which is 400.
      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous your following statement is confusing

         

        so for both tables Max date is 30/11/2019 and second max date is 31/10/2019.

         

        for Dataset 1 , max date is 18/12/2019 instead of 30/11/2019, am I missing something here?