Calculate current year forecast YTD sales to display on Line Chart to compare All last 3 YTD Sales

[Anonymous]

Hello All

Need help in DAX to calculate current year forecast YTD sales to display on Line Chart.

I've attached the PBIX file with the Date, Sales, Year table.

https://drive.google.com/file/d/1YFDcWd6CREtfBuqnO-zFqQeNQSwMj1BM/view?usp=sharing 

You can see all last three YTD 2018, 2019, 2020 are plotted on the single graph to compare against 2021 YTD. 

The desired output that i like to achieve is (preferably first), if not then the 2nd optional output

1. To calculate current year forecast YTD sales to display on Line Chart. To display a line graph showing sales till current week 15 and Week 16 to 52 with forecast data (need help in creating Forecast DAX). So sales graph line (Orange 2021) must show up to the current week 15 with real data and from week 16 onwards forecast data, which dynamically change from week 16 going forward so on and so forth.

2. If the first desired output not possible, can we try this one. Presently the current year YTD sales are only populated up to week 15 because weeks 16 – 52 have not happened yet. From Week 16 2021 sales are flat-lining till week 52. How can I tweak the current DAX to stop at week 15 the 2021 YTD Orange line?

Would be very grateful if you can help me find thr DAX for Forecast for remaning week or else the modify my current DAX to stop at week 15 the 2021 YTD Orange line?

Thank you so much, 

[Jihwan_Kim]

Hi, @Anonymous 

I could not open your file because the link asked me to enter a password.

Please provide another link.

Thank you.

[Anonymous]

https://drive.google.com/file/d/1KfQfWMP3eKebZoF7R2Xmz6Cce4RXsSKD/view?usp=sharing 

hope this will work - do let me know pls

[Anonymous]

Try again, pls. https://drive.google.com/file/d/1YFDcWd6CREtfBuqnO-zFqQeNQSwMj1BM/view?usp=sharing

[Jihwan_Kim]

Hi @Anonymous 

For your YTD measure, please try the below.

SCE YTD $ = IF( not ISBLANK( [SCE_Sales $]), TOTALYTD([SCE_Sales $],'Date'[Date]), BLANK())

Then, your VEW ALL measure for 2021 will show until the current value.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

Thanks for your reply. This is how the view looking like, may be months gaps are showing up. Where the desired output is to show all last 3 years YTD and current year till week 15 and then continued.  

[Jihwan_Kim]

Hi,

The link still asks to enter a password.

[Anonymous]

Very strange @Jihwan_Kim , google drive access working at my end. Will send you the screen shot so that you can see the dataset and model overview. SCE TABLE

DATA MODEL

DATE TABLE

SCE_Sales $ = SUMX(SCE,SCE[Sales $])

1. SCE YTD $ = TOTALYTD([SCE_Sales $],'Date'[Date])

2. View (All Year YTD) =

3. VAR YearsBack = year(today()) - SELECTEDVALUE('Year'[Year])

RETURN

CALCULATE([SCE YTD $],

DATEADD('Date'[Date], -YearsBack, YEAR)

Used three DAXs and Year TBL as Value from Date Table. 

Let me know if you need any more information. Thank