Forum Discussion

NickDSL's avatar
NickDSL
Helper I
2 years ago
Solved

Comparing MoM Data where Users can select current month

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

 

GLPeriodPeriodYTDIsCurrentMonthBase Act OrderTypeKey
Base Act - 117/1/2023  1ACTUALACTUAL7/1/2023
Base Act - 108/1/2023  2ACTUALACTUAL8/1/2023
Base Act - 99/1/2023  3ACTUALACTUAL9/1/2023
Base Act - 8########  4ACTUALACTUAL10/1/2023
Base Act - 7########  5ACTUALACTUAL11/1/2023
Base Act - 6########  6ACTUALACTUAL12/1/2023
Base Act - 51/1/20241 7ACTUALACTUAL1/1/2024
Base Act - 42/1/20241 8ACTUALACTUAL2/1/2024
Base Act - 33/1/20241 9ACTUALACTUAL3/1/2024
Base Act - 24/1/20241 10ACTUALACTUAL4/1/2024
Base Act - 15/1/20241 11ACTUALACTUAL5/1/2024
Base Act6/1/20241112ACTUALACTUAL6/1/2024
Bud 11/1/20241  BUDGETBUDGET1/1/2024
Bud 22/1/20241  BUDGETBUDGET2/1/2024
Bud 33/1/20241  BUDGETBUDGET3/1/2024
Bud 44/1/20241  BUDGETBUDGET4/1/2024
Bud 55/1/20241  BUDGETBUDGET5/1/2024
Bud 66/1/202411 BUDGETBUDGET6/1/2024
Bud 77/1/2024   BUDGETBUDGET7/1/2024
Bud 88/1/2024   BUDGETBUDGET8/1/2024
Bud 99/1/2024   BUDGETBUDGET9/1/2024
Bud 10########   BUDGETBUDGET10/1/2024
Bud 11########   BUDGETBUDGET11/1/2024
Bud 12########   BUDGETBUDGET12/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!
"Need Help"
  • NickDSL's avatar
    NickDSL
    2 years ago

    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)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

  • NickDSL's avatar
    NickDSL
    Helper I
    2 years ago

    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)