Forum Discussion
Solved
Create a dynamic reference line for tracking 12 months
As a beginner, I searched lots of articles, and it worked out with showing the visual data for tracking 12 months. Now there is another problem, which is, I would like to use the last-month average ...
Hi ryan_mayu
Try below measure:
Measure = VAR CurrentDate = MAX ( 'Date-2'[Date] ) VAR PreviousDate = DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, 1 ) RETURN IF ( MAX ( 'Date'[Date] ) >= PreviousDate && MAX ( 'Date'[Date] ) <= CurrentDate, CALCULATE ( [Demand Charge per Train] + [Energy Charge per Train], FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = CurrentDate ) ) )Regards,
Cherie
Thank all of your replies.
I made a little mistake about what I want. Please allow me to explain again.
The average line is a value of the last month.
The formula is as below.
[Trailing 12-month accumulative Energy Cost] / [Trailing 12-month Trains]
Take "Nov-2018" as example, the average value should be 28,016 dollars.
Originally, I tried to use LOOKUPVALUE, but it cannot be shown only between 2017-12 to 2018-11.
Anyway, I revised the pbix file. I really need your hlep indeed.
Thank you.
Hi julie_tai
Try below measure:
Measure =
VAR CurrentDate =
MAX ( 'Date(Irrelevant)'[Date] )
VAR PreviousDate =
DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 11, 1 )
RETURN
IF (
MAX ( 'Date'[Date] ) >= PreviousDate
&& MAX ( 'Date'[Date] ) <= CurrentDate,
CALCULATE (
[TTM Average Energy Cost per Train],
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = CurrentDate )
)
)
Regards,
Cherie