Forum Discussion
Date/ Week Linear Forecast Line Issue
Since you are using a fiscal calendar and the year start date is not constant (e.g., Nov 1st), I would write your measures like this. Note that your sample file did not have data for the previous year so it was hard to confirm. Also note that the IF in the Return is to limit the visual to just the current FY (instead of using the visual-level filter).
CY PAV =
VAR maxdate =
MAX ( 'Date'[Date Key] )
VAR currentyear =
CALCULATE ( MAX ( 'Date'[FY Num] ), 'Date'[Date Key] = TODAY () )
VAR result =
CALCULATE (
SUM ( Fact[Peak Annual Revenue$] ),
ALL ( 'Date' ),
'Date'[FY Num] = currentyear,
'Date'[Date Key] <= maxdate
)
RETURN
IF ( MAX ( 'Date'[FY Num] ) = currentyear, result )
The PY one is very similar but subtract 1 in two places.
PY PAV =
VAR maxdate =
MAX ( 'Date'[Date Key] )
VAR currentyear =
CALCULATE ( MAX ( 'Date'[FY Num] ), 'Date'[Date Key] = TODAY () )
VAR result =
CALCULATE (
SUM ( Fact[Peak Annual Revenue$] ),
ALL ( 'Date' ),
'Date'[FY Num] = currentyear - 1,
'Date'[Date Key]
<= DATE ( currentyear - 1, MONTH ( maxdate ), DAY ( maxdate ) )
)
RETURN
IF ( MAX ( 'Date'[FY Num] ) = currentyear, result )
Pat