Pulling in prior month data to create a calculation

[reboundgt9]

I need to be create two measures so that I can create a third % change measure. The data measures needed are (1) current month membership and (2) prior month membership. The current month membership is determined by the largest end date selected from within a slicer on the page. I have been able to successfully pull in the enrollment for all groups in the current months membership, however, I have not been able to get the data from one month immediately proceeding the max end date.

I have tried to amend the below dax to include a '- 1' after the max period date but have not been able to get that to work.

current month dax - 

Current Month Membership = CALCULATE(SUM(Enrollment[Member Count]),FILTER('Enrollment',Enrollment[Period Date] = MAX('Enrollment'[Period Date])))

I'm not sure if this matters but [Period Date] is a calendar date value in the data.

Any help is appreciated, thanks!!

[v-yetao1-msft · Accepted solution]

Hi @reboundgt9 

You can use EOMONTH() to return the value that you want . Returns the date in datetime format of the last day of the month, before or after a specified number of months. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

(1)Create a Calendar Date table to filter date in Enrollment table.

Calendar Date = CALENDAR(DATE(2021,01,01),DATE(2021,12,31))

(2)Add a slicer with field 'Calendar Date'[Date] .

(3)Create two measures to return the value from current month and previous month .

Current Month Membership = CALCULATE(SUM(Enrollment[Member Count]),FILTER(Enrollment,Enrollment[Period Date]>EOMONTH(MAX('Calendar Date'[Date]),-1) && Enrollment[Period Date]<=EOMONTH(MAX('Calendar Date'[Date]),0)))

Previous Month Membership = CALCULATE(SUM(Enrollment[Member Count]),FILTER(Enrollment,Enrollment[Period Date]>EOMONTH(MAX('Calendar Date'[Date]),-2) && Enrollment[Period Date]<=EOMONTH(MAX('Calendar Date'[Date]),-1)))

(4)Add card visual to display the measures .

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[lbendlin]

Your current month measure doesn't need to be that complicated.

Current Month Membership = SUM(Enrollment[Member Count])

And for the prior month you can use the standard time intelligence functions

Prior Month Membership = CALCULATE(SUM(Enrollment[Member Count]),DATEADD('Enrollment'[Period Date],-1,MONTH))