topic Forecast WOW and change rate in different versions in DAX Commands and Tips

Forecast WOW and change rate in different versions

Khalefa — Thu, 08 Dec 2022 13:25:05 GMT

Hi there

I just need some help in creating two measures ( WOW forecast per each forecast version, change rate% for the same week in different forecast version

Statement

On a weekly basis, I receive a forecast version with the upcoming 12 weeks and for the next week, I receive another version of 12 weeks but we drop the oldest week and add a new week to that version, etc.

Support needed

1- create a dax to calculate the previous week's forecast &WOW% ( but within the same version)

2- Create a nother dax to calculate the change rate for the same week but in the different versions

Example

If week 46 repeats in 3 versions ( v-43 , V-44, V-45,V-46 ) i need to know the change rate trend line :

- (Week 46 in V-44 - Week 46 in V-43 ) / Week 46 in V-43

-Then (Week 46 in V-45 - Week 46 in V-44 ) / Week 46 in V-44 etc.

3- I have a date table connected to the fact table as well ( ISO week based )

4- As you may notice that the weeks forecast repeat in differrent versions, so do you see that keep them in one fact table or create a dim table for the week version? if so does this will cause any issue with the calendar date interms of filter and any other issues?

Sample of Data

Week version	Week	forecast
V-W43	W43	5648
V-W43	W44	9742
V-W43	W45	9631
V-W43	W46	9571
V-W43	W47	5029
V-W43	W48	6744
V-W43	W49	9652
V-W43	W50	5577
V-W43	W51	5597
V-W43	W52	4861
V-W43	W1	3716
V-W43	W2	7869
V-W44	W44	7251
V-W44	W45	4505
V-W44	W46	8263
V-W44	W47	3254
V-W44	W48	4467
V-W44	W49	4962
V-W44	W50	8317
V-W44	W51	3922
V-W44	W52	8128
V-W44	W1	7376
V-W44	W2	6125
V-W44	W3	4566
V-W45	W4	4292
V-W45	W46	4148
V-W45	W47	3678
V-W45	W48	5069
V-W45	W49	8275
V-W45	W50	9874
V-W45	W51	4458
V-W45	W52	7879
V-W45	W1	5388
V-W45	W2	7319
V-W45	W3	8500
V-W45	W4	9172
V-W45	W5	7613

Re: Forecast WOW and change rate in different versions

amitchandak — Thu, 08 Dec 2022 14:54:30 GMT

Khalefa , with help from week rank on week start date or year week you can create week measures

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
WeekDay = weekday([Date])

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

2nd Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-2))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Forecast WOW and change rate in different versions

Khalefa — Thu, 08 Dec 2022 14:59:51 GMT

amitchandak thanks, IS it possible to show me an example of the data set I shared by creating the approach to answer my questions? would be really appreciated