topic Re: Calculate current year forecast YTD sales to display on Line Chart to compare All last 3 YTD Sales in DAX Commands and Tips

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

Anonymous — Sun, 04 Apr 2021 22:23:54 GMT

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,

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

Jihwan_Kim — Mon, 05 Apr 2021 03:11:57 GMT

Hi, Anonymous

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

Please provide another link.

Thank you.

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

Anonymous — Mon, 05 Apr 2021 08:13:28 GMT

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

hope this will work - do let me know pls

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

Jihwan_Kim — Mon, 05 Apr 2021 08:29:06 GMT

Hi,

The link still asks to enter a password.

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

Anonymous — Mon, 05 Apr 2021 08:39:05 GMT

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

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

Jihwan_Kim — Mon, 05 Apr 2021 09:46:49 GMT

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.

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

Anonymous — Mon, 05 Apr 2021 11:12:56 GMT

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.

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

Anonymous — Mon, 05 Apr 2021 11:22:12 GMT

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_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