Forum Discussion

Ajaal's avatar
Ajaal
Helper I
2 years ago

Previous date's value

How do I create a measure that calculates and retrives the average of previous date values whenever the new data's corresponding values are added to the underlying dataset of the report? I wanna use ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi  Ajaal ,

 

You can try formula like below:

M_ =
VAR LastAddedMonth =
    CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) )
VAR LastMonth =
    MONTH ( LastAddedMonth ) - 1
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Prodction] ),
        MONTH ( 'Table'[Date] ) = LastMonth
    )

 

Best Regards,
Adamk Kong

 

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

  • Ajaal's avatar
    Ajaal
    Helper I
    2 years ago
    Thnx for replying Mr.Kong...
    The formula you've provided didn't work for me, I did not see the result of that measure M_ when adding it to a Card chart  (average value was blank)
     
    VAR LastMonth = MONTH ( LastAddedMonth ) - 1 ==>this part of your Formula does not seem to work for me, when I tried testing the value retrieved by this part as 
     
    M_ =
    VAR LastAddedMonth = CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) )
    VAR LastMonth = MONTH ( LastAddedMonth ) - 1
    RETURN
       LastMonth
     
     returns 2 (not a Date Value)
     
    whereas the first part
    M_ =
    VAR LastAddedMonth = CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) )
    VAR LastMonth = MONTH ( LastAddedMonth ) - 1
    RETURN
      LastAddedMonth
     
     returns
        3/1/2024