Forum Discussion

NickDSL's avatar
NickDSL
Icon for Helper I rankHelper I
2 years ago
Solved

Comparing MoM Data where Users can select current month

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...
"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)
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.