How to calculate cumulative wow values

[Anonymous]

Item	Week	Score	Responses
1	551	5	4
1	552	5	6
1	553	6	8
1	554	3	2
2	551	6	20
2	552	9	10
2	553	5	23
2	554	9	11
3	551	6	23
3	552	8	33
3	553	6	56
3	554	9	8

I have a dataset something like this. 

I want to calculate the WoW change of this data at item level. Let's suppose the items are columns in my output and if I select week 552, I sohuld get a WoW against the current value. 

The catch here is that I want to make it dynamic. If I select two weeks here from the filter pane, I should be able to see the aggregated score(weighted mean) of those two weeks and the WoW should be the score of previous two weeks(aggregated as weighted mean).

Example: If I select week 553 and 554, I should see the wow as (aggregation of week 553 and 554) - (aggregation of week 552 and 551)

I am able to do a simple WoW(one week comparison) using DAX but not able to figure out how to do it if multiple weeks are selected.

I was thinking of doing a countdistinct of the weeks selected in filter and calculate the minimum week(min_week) of the selection. Then, doing a weighted mean from min_week-countdistinct to min_week - 1.

Can somebody help me please

Note: Weeks would always be selected consecutively

DAX formula help @Anonymous @Kpower @Anonymous @nepow @dax 

[v-lili6-msft]

hi, @Anonymous 

Just try this formula to get dynamic previous week value

Measure = 
VAR weeks =
    CALCULATE ( COUNTA ( 'Table'[Week] ), ALLSELECTED ( 'Table'[Week] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Score] ),
        FILTER (
            ALL ( 'Table'[Week] ),
            CONTAINS ( VALUES ( 'Table'[Week] ), 'Table'[Week], 'Table'[Week] + weeks )
        )
    )

Result:

If selected 552, it will return data in week 551 of each Item.

and if selected 553 and 554, it will return data in 551 and 552 of each item.

Then you could use it for other calculation.

and here is sample pbix, please try it.

Best Regards,

Lin

[Anonymous]

Hi  @v-lili6-msft,

Thank you for replying.

A question here, instead of a simple sum, can I do a SUMX as I need weighted mean of the score and responses.

In the calculate tab, can I write SUMX(Score*responses)/sum(responses)

Can this be done?

Regards,

[v-lili6-msft]

hi, @Anonymous 

Yes, you could try it.

If you still have the problem, please share your expected output as below:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[Anonymous]

hi @v-lili6-msft ,

Thanks for your reply.

I will go about explaining my problem once again

My data is in this format as below:

Item	Week	Driver	Score	Resp
1	551	D0	6	98
1	551	D1	6.87	189
1	551	D2	6.67	63
1	551	D3	5.3	70
1	552	D0	6.92	182
1	552	D1	7.24	133
1	552	D2	4.57	49
1	552	D3	6.25	70
3	551	D0	7.8	196
3	551	D1	7.32	77
3	551	D2	4.97	105
3	551	D3	5.07	161
3	552	D0	5.31	112
3	552	D1	11.3	70
3	552	D2	5.83	42
3	552	D3	5.55	133
4	551	D0	4.4	70
4	551	D1	5.04	91
4	551	D2	8.27	469
4	551	D3	3.1	70
4	552	D0	3.86	49
4	552	D1	15	35
4	552	D2	8.71	133
4	552	D3	6.21	49
7	551	D0	8.63	119
7	551	D1	10.89	7
7	551	D2	4.57	49
7	551	D3	10.69	35
7	552	D0	6	98
7	552	D1	6.87	189
7	552	D2	6.67	63
7	552	D3	5.3	70

I want  to calculate the WoW values of the item at item level.

The values should be in a crosstab as below:

item	D0	D0 - WoW	D1	D1 -WoW	D2	D2 - WoW	D3	D3 - WoW
1	9		8		8		8
3	9		9		8		9
4	8		9		9		9
7	7		10		9		10

If I select week 552 from the filter, then column D0,D1,D2,D3 in table 2 should show the weighted sum of score and responses and the D0-WoW, D1-WoW, D2-WoW and D3-WoW columns should show the weighted sum of week 551.

What I want is that if I select week 553 and 554, then I should get the WoW column to show the weighted mean of 551 and 552. My data is for 52 weeks. Similarly, selecting 3 consecutive weeks should give me the weighted mean of previous 3 weeks corresponding to the item and driver(D0,D1,D2,D3) and so on.

Weighted mean is calculated as: sumx(table, score*response)/sum(response)

Appreciate your help!!

Regards,

[Anonymous]

Also, another follow-up question, do all these features work in v2.8?

---

@Anonymous wrote:

Hi  @v-lili6-msft,

Thank you for replying.

A question here, instead of a simple sum, can I do a SUMX as I need weighted mean of the score and responses.

In the calculate tab, can I write SUMX(Score*responses)/sum(responses)

Can this be done?

Regards,

---