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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k  I only build my reports once the month is closed. So for this purpose, my max date should be 30 Nov, once we have closed December My max date will be 31st December. hope that makes sense.