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