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