DAX to calculate Forecast change in different week versions

[Khalefa]

Hi there

i just need some help according to the below description

1- I receive a forecast version every week with the upcoming 12 weeks and in every version, we drop one older and add a new week forecast to the new version

2- sample is provided with the data and the needed Dax

 3- want to compute WOW in each version of the forecast

4- then want to calculate the change rate for each week in all the versions of the forecast

5- need to have a trend line for the above point

Request 1	Week version	Week	Previous week forecast
		V-W43	W43	56017
		W44	39376
		W45
		W46	?
		W47	?
		W48	?
		W49	?
		W50	?
		W51	?
		W52	?
		W1	?
		W2	?

Request2
	Week	V-W44	V-W45	V-W46
	W44	39376	50194
	W45
	W46
	W47
	W48
	W49
	W50
	W51
	W52
	W1
	W2
	W3

Request3	Week	V-W44	V-W45
	W44		27%
	W45
	W46
	W47
	W48
	W49
	W50
	W51
	W52
	W1
	W2
	W3

data sample

Week versionWeekDayForecast

V-W43	W43	10/24/2022	5648
V-W43	W43	10/25/2022	9742
V-W43	W43	10/26/2022	9631
V-W43	W43	10/27/2022	9571
V-W43	W43	10/28/2022	5029
V-W43	W43	10/29/2022	6744
V-W43	W43	10/30/2022	9652
V-W43	W44	10/31/2022	5577
V-W43	W44	11/1/2022	5597
V-W43	W44	11/2/2022	4861
V-W43	W44	11/3/2022	3716
V-W43	W44	11/4/2022	7869
V-W43	W44	11/5/2022	7251
V-W43	W44	11/6/2022	4505
V-W43	W45	11/7/2022	8263
V-W43	W45	11/8/2022	3254
V-W43	W45	11/9/2022	4467
V-W43	W45	11/10/2022	4962
V-W43	W45	11/11/2022	8317
V-W43	W45	11/12/2022	3922
V-W43	W45	11/13/2022	8128
V-W43	W46	11/14/2022	7376
V-W43	W46	11/15/2022	6125
V-W43	W46	11/16/2022	4566
V-W43	W46	11/17/2022	4292
V-W43	W46	11/18/2022	4148
V-W43	W46	11/19/2022	3678
V-W43	W46	11/20/2022	5069
V-W43	W47	11/21/2022	8275
V-W43	W47	11/22/2022	9874
V-W43	W47	11/23/2022	4458
V-W43	W47	11/24/2022	7879
V-W43	W47	11/25/2022	5388
V-W43	W47	11/26/2022	7319
V-W43	W47	11/27/2022	8500
V-W43	W48	11/28/2022	9172
V-W43	W48	11/29/2022	7613
V-W43	W48	11/30/2022	9009
V-W43	W48	12/1/2022	3332
V-W43	W48	12/2/2022	8579
V-W43	W48	12/3/2022	6889
V-W43	W48	12/4/2022	9968
V-W43	W49	12/5/2022	9079
V-W43	W49	12/6/2022	3430
V-W43	W49	12/7/2022	3115
V-W43	W49	12/8/2022	9024
V-W43	W49	12/9/2022	7045
V-W43	W49	12/10/2022	4086
V-W43	W49	12/11/2022	5662
V-W43	W50	12/12/2022	3785
V-W43	W50	12/13/2022	9720
V-W43	W50	12/14/2022	8653
V-W43	W50	12/15/2022	7944
V-W43	W50	12/16/2022	6001
V-W43	W50	12/17/2022	4686
V-W43	W50	12/18/2022	5769
V-W43	W51	12/19/2022	7651
V-W43	W51	12/20/2022	4973
V-W43	W51	12/21/2022	7938
V-W43	W51	12/22/2022	9714
V-W43	W51	12/23/2022	5484
V-W43	W51	12/24/2022	4959
V-W43	W51	12/25/2022	3682
V-W43	W52	12/26/2022	5824
V-W43	W52	12/27/2022	7256
V-W43	W52	12/28/2022	8934
V-W43	W52	12/29/2022	8231
V-W43	W52	12/30/2022	6306
V-W43	W52	12/31/2022	4224
V-W43	W52	1/1/2023	9254
V-W43	W1	1/2/2023	5598
V-W43	W1	1/3/2023	8035
V-W43	W1	1/4/2023	8971
V-W43	W1	1/5/2023	8451
V-W43	W1	1/6/2023	4240
V-W43	W1	1/7/2023	9424
V-W43	W1	1/8/2023	5246
V-W43	W2	1/9/2023	3536
V-W43	W2	1/10/2023	3723
V-W43	W2	1/11/2023	6508
V-W43	W2	1/12/2023	8161
V-W43	W2	1/13/2023	3224
V-W43	W2	1/14/2023	5575
V-W43	W2	1/15/2023	8183
V-W44	W44	10/31/2022	6705
V-W44	W44	11/1/2022	4831
V-W44	W44	11/2/2022	6712
V-W44	W44	11/3/2022	9054
V-W44	W44	11/4/2022	4880
V-W44	W44	11/5/2022	9750
V-W44	W44	11/6/2022	8262
V-W44	W45	11/7/2022	6220
V-W44	W45	11/8/2022	6666
V-W44	W45	11/9/2022	3513
V-W44	W45	11/10/2022	6036
V-W44	W45	11/11/2022	5669
V-W44	W45	11/12/2022	3390
V-W44	W45	11/13/2022	5497
V-W44	W46	11/14/2022	9656
V-W44	W46	11/15/2022	6206
V-W44	W46	11/16/2022	6322
V-W44	W46	11/17/2022	9103
V-W44	W46	11/18/2022	5968
V-W44	W46	11/19/2022	7016
V-W44	W46	11/20/2022	3115
V-W44	W47	11/21/2022	6130
V-W44	W47	11/22/2022	7936
V-W44	W47	11/23/2022	9388
V-W44	W47	11/24/2022	6716
V-W44	W47	11/25/2022	3001
V-W44	W47	11/26/2022	4624
V-W44	W47	11/27/2022	7567
V-W44	W48	11/28/2022	9537
V-W44	W48	11/29/2022	4935
V-W44	W48	11/30/2022	9776
V-W44	W48	12/1/2022	8037
V-W44	W48	12/2/2022	7076
V-W44	W48	12/3/2022	7846
V-W44	W48	12/4/2022	4597
V-W44	W49	12/5/2022	8441
V-W44	W49	12/6/2022	6714
V-W44	W49	12/7/2022	6678
V-W44	W49	12/8/2022	4241
V-W44	W49	12/9/2022	5698
V-W44	W49	12/10/2022	7848
V-W44	W49	12/11/2022	4031
V-W44	W50	12/12/2022	8737
V-W44	W50	12/13/2022	7110
V-W44	W50	12/14/2022	3040
V-W44	W50	12/15/2022	3868
V-W44	W50	12/16/2022	5294
V-W44	W50	12/17/2022	4997
V-W44	W50	12/18/2022	9301
V-W44	W51	12/19/2022	5930
V-W44	W51	12/20/2022	4655
V-W44	W51	12/21/2022	6481
V-W44	W51	12/22/2022	3178
V-W44	W51	12/23/2022	9633
V-W44	W51	12/24/2022	4792
V-W44	W51	12/25/2022	7116
V-W44	W52	12/26/2022	5975
V-W44	W52	12/27/2022	8426
V-W44	W52	12/28/2022	4825
V-W44	W52	12/29/2022	5889
V-W44	W52	12/30/2022	6646
V-W44	W52	12/31/2022	3440
V-W44	W52	1/1/2023	5311
V-W44	W1	1/2/2023	5336
V-W44	W1	1/3/2023	7763
V-W44	W1	1/4/2023	9124
V-W44	W1	1/5/2023	3933
V-W44	W1	1/6/2023	8333
V-W44	W1	1/7/2023	3086
V-W44	W1	1/8/2023	3711
V-W44	W2	1/9/2023	6120
V-W44	W2	1/10/2023	4264
V-W44	W2	1/11/2023	3160
V-W44	W2	1/12/2023	7279
V-W44	W2	1/13/2023	4641
V-W44	W2	1/14/2023	3712
V-W44	W2	1/15/2023	9510
V-W44	W3	1/16/2023	9042
V-W44	W3	1/17/2023	4982
V-W44	W3	1/18/2023	3162
V-W44	W3	1/19/2023	9696
V-W44	W3	1/20/2023	8238
V-W44	W3	1/21/2023	9145
V-W44	W3	1/22/2023	7798

[Anonymous]

Hi @Khalefa ,

You may create a following measure. The calculation logic is to get the forecast values of the current week and the previous week according to the week version, respectively, and then get the percentage.

WOW =
VAR _CUR =
    CALCULATE (
        SUM ( 'Table'[Forecast] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Week version] = MAX ( 'Table'[Week version] )
                && [Week] = MAX ( 'Table'[Week] )
        )
    )
VAR _PRE =
    CALCULATE (
        SUM ( 'Table'[Forecast] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Week version] = MAX ( 'Table'[Week version] )
                && VALUE ( RIGHT ( [Week], LEN ( [Week] ) - 1 ) )
                    = VALUE ( RIGHT ( MAX ( 'Table'[Week] ), LEN ( MAX ( 'Table'[Week] ) ) - 1 ) ) - 1
        )
    )
RETURN
    DIVIDE ( _CUR - _PRE, _PRE )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Khalefa]

Thanks @Anonymous for the support

i still need to calculate the change% for a specific week in all week versions ( trendline)

by other means, 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. 

[Khalefa]

 Sorry the sample some how corrupted when I added the tables. If it is clear.  Let me know