Comparing MoM Data where Users can select current month

[NickDSL]

Hi we have the following date table called "dGLDates". 

GLPeriod	Period	YTD	IsCurrentMonth	Base Act Order	Type	Key
Base Act - 11	7/1/2023			1	ACTUAL	ACTUAL7/1/2023
Base Act - 10	8/1/2023			2	ACTUAL	ACTUAL8/1/2023
Base Act - 9	9/1/2023			3	ACTUAL	ACTUAL9/1/2023
Base Act - 8	########			4	ACTUAL	ACTUAL10/1/2023
Base Act - 7	########			5	ACTUAL	ACTUAL11/1/2023
Base Act - 6	########			6	ACTUAL	ACTUAL12/1/2023
Base Act - 5	1/1/2024	1		7	ACTUAL	ACTUAL1/1/2024
Base Act - 4	2/1/2024	1		8	ACTUAL	ACTUAL2/1/2024
Base Act - 3	3/1/2024	1		9	ACTUAL	ACTUAL3/1/2024
Base Act - 2	4/1/2024	1		10	ACTUAL	ACTUAL4/1/2024
Base Act - 1	5/1/2024	1		11	ACTUAL	ACTUAL5/1/2024
Base Act	6/1/2024	1	1	12	ACTUAL	ACTUAL6/1/2024
Bud 1	1/1/2024	1			BUDGET	BUDGET1/1/2024
Bud 2	2/1/2024	1			BUDGET	BUDGET2/1/2024
Bud 3	3/1/2024	1			BUDGET	BUDGET3/1/2024
Bud 4	4/1/2024	1			BUDGET	BUDGET4/1/2024
Bud 5	5/1/2024	1			BUDGET	BUDGET5/1/2024
Bud 6	6/1/2024	1	1		BUDGET	BUDGET6/1/2024
Bud 7	7/1/2024				BUDGET	BUDGET7/1/2024
Bud 8	8/1/2024				BUDGET	BUDGET8/1/2024
Bud 9	9/1/2024				BUDGET	BUDGET9/1/2024
Bud 10	########				BUDGET	BUDGET10/1/2024
Bud 11	########				BUDGET	BUDGET11/1/2024
Bud 12	########				BUDGET	BUDGET12/1/2024

The user has a slicer where they can select a period on the "Period" column.

I am trying to build a series of measures that would compute a dynamic "last month" so for example:

If 6/1/2024 is selected then the measure would read 5/1/2024. 

This is an example measure of what I have so far but its not working/doesnt return the expected result:

Avg Metric LM = CALCULATE([Avg Metric Act],ALL(dGL_Dates),dGL_Dates[Base Act Order] - 1)

My thinking is that if user has 6/1/2024 which is [Base Act Order] = 12 then the above should point it to [Base Act Order] = 11 which would be 5/1/2024. My measure does not do this and returns a complete unexpected result. 

This returns expected result but does not have the dynamic features I need for when a user selects a different period:

Avg Metric LM = CALCULATE([Avg Metric Act],ALL(dGL_Dates),dGL_Dates[Base Act Order] = 11)

Any help is appreciated!

[NickDSL · Accepted solution]

Solution I came up with that works:

Avg Metric LM =

Var SelectedDate= SELECTEDVALUE(dGL_Dates[Period])

Var LastMonth = DATE(YEAR(SelectedDate), MONTH(SelectedDate) - 1, 1)

Return

CALCULATE(

    CALCULATE([Avg Metric Act]), dGL_Dates[Period] = LastMonth)

[v-zhouwen-msft]

Hi @NickDSL ,

Regarding your question, when the user selects '6/1/2024', there are multiple values. It is recommended that you modify your expression.

Like this

Measure = 
VAR _Order = MAX('dGLDates'[Base Act Order]) -1
VAR _result = CALCULATE(MAX('dGLDates'[Period]),ALL(dGLDates[Period]),'dGLDates'[Base Act Order] = _Order)
RETURN IF(_Order = 0,BLANK(),_result)

Final output

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