Date/ Week Linear Forecast Line Issue

[letiwang]

Hi experts,

My goal is to set a trend chart to show multiple lines in single visual:

1. this year's actual sales

2. this year's sales forecast line

3. previous year's actual sales

So I wrote Dax to make 1) 2), but I faced another problem that I couldn't show previous sales in this chart since the x-axis is the date of this year. Details please reference to the sample file attached. 

Now I'm thinking some possible solutions, any advise or suggestion would be appreciated. Thanks!

A. Change the x-axis to "FW sum" and write specific year measures in this visual.

In fact I've tried to use "FW sum" in Forecast line2, but it failed to show correct regression line. Not sure what's the problem because the formula works fine in "Date". (pls see below pic as the situation)

B. Write another measure to show cumulative sales in previous year, maybe Time Intelligence Funtion?
If possible, I'd prefer Solution A because we use fiscal calender. I found that PREVIOUSYEAR funcion might not correspond to every date, especially at the start/end of FY. But I'm still wondering how to acheived it too.

C. Is there any more easy way to reach my goal? Think it might good to make it more simple.

Thanks! Here's the sample file for reference.

[letiwang]

Hi Pat,

Thanks so much for your feedback. To make it more realistic, I add some data in previous year so we could have a check.

I used the method and got the results as imagined. Though not sure why "PY PAV_suggest" shows incorrect value in Nov-Dec, I tried to fix it like this .

PY PAV_mytest = 
VAR currentyear =
    CALCULATE (  MAX('Date'[FY Num]) , 'Date'[Date Key] = TODAY () )
VAR maxdate =
    CALCULATE(MAX ( 'Date'[Date Key] ), 'Date'[FY Num] = currentyear)
VAR result =
    CALCULATE (
        SUM ( Fact[Peak Annual Revenue$] ),
        ALL ( 'Date' ),
        'Date'[Date Key]
            <= DATE ( YEAR(maxdate)-1, MONTH ( maxdate ), DAY ( maxdate ) )
    )
RETURN
    IF ( MAX ( 'Date'[FY Num] ) = currentyear, result )

Another question is that I found total PAV in previous year would be incorrect due to the fiscal calendar.

For expample, 2022/10/30 is in FY23 but 2021/10/30 is in FY22.

So in the sample file, we got 0.56bn as previous year total, but the actual value shoud be 0.54bn.

Does this mean we should try to change x-axis to FW num, or we can fix it in DAX?

*New sample file: https://drive.google.com/file/d/11SwA56S5CcZHw70Si7-iR45zL7HVJ8Cs/view?usp=sharing

Thanks!

[ppm1]

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