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 the both data set my Max Date should be 31/11/2019 because we haven't closed December yet, and the value I would like to get is the green highlights.

 

I also have a calendar table merged with reporting date. I was thinking first I should create a measure that will give me Max date in the data set and then write another one to get the value of previous month.

Could you pls help me with the DAX formula for that purpose?

Thank you 

Duygu

 

 

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.

7 Replies

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

  • v-xicai's avatar
    v-xicai
    Community Support
    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.