Getting Prevous Month Value Based on Max Date

[Anonymous]

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

[v-xicai · Accepted solution]

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]

@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]

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]

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

[Anonymous]

@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]

@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]

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