Help with Average Weekly DAX formula by comparing same period last year

[echow]

I need to create a weekly sales by member comparing between 2020 and 2019.

The part I am struggling is how to obtain the LY Count column for both A and B category in DAX

The desire outcome table of my data is something like this

My final desire outcome is for the bar chart to look like something below.

So if I were to choose my filter 202042 and 202043 it will give me the outcome below

Calculation for Category A would be 

Blue Bar: (112.58+65.79)/(8+7) = $11.89

Orange Bar: (172.69+68.23)/(17+6) = $10.47

And so forth for Category B

So if I were to dynamically select 202043 only then it should be giving me

Blue Bar: 65.79/7 = $9.4

Orange Bar: 68.23/6 = $11.37

Here is my set of data

CALENDAR_DATE	MBR_NO	Sales	Item	Category
14/10/2019	1	$        10.00	A1	A
14/10/2019	3	$          2.00	B3	B
14/10/2019	3	$          3.00	B3	B
14/10/2019	4	$          7.65	A4	A
14/10/2019	5	$        19.66	A5	A
15/10/2019	6	$          0.57	A6	A
15/10/2019	7	$        10.34	A7	A
15/10/2019	9	$          1.91	A9	A
15/10/2019	13	$          7.18	A13	A
15/10/2019	14	$        10.46	B14	B
15/10/2019	14	$          0.13	B14	B
16/10/2019	18	$          5.67	A18	A
16/10/2019	18	$          7.45	A18	A
16/10/2019	21	$          5.37	A21	A
17/10/2019	22	$        15.03	A22	A
17/10/2019	23	$          8.45	B23	B
17/10/2019	24	$        17.73	B24	B
17/10/2019	33	$          8.16	B33	B
18/10/2019	36	$          2.71	B36	B
18/10/2019	37	$        13.85	B37	B
18/10/2019	40	$          1.66	A40	A
18/10/2019	40	$        13.41	A40	A
18/10/2019	40	$          5.60	B40	B
18/10/2019	41	$        17.83	B41	B
18/10/2019	42	$        19.64	A42	A
19/10/2019	43	$        10.17	A43	A
19/10/2019	44	$        14.85	B44	B
19/10/2019	44	$        13.47	A44	A
20/10/2019	51	$          0.28	A51	A
20/10/2019	52	$          3.53	B52	B
20/10/2019	55	$        18.06	A55	A
20/10/2019	56	$          5.18	A56	A
21/10/2019	57	$          1.29	B57	B
21/10/2019	57	$          6.35	B57	B
21/10/2019	57	$        13.77	B57	B
21/10/2019	60	$          2.14	B60	B
21/10/2019	63	$          3.72	B63	B
24/10/2019	64	$        17.73	A64	A
24/10/2019	65	$          7.52	A65	A
24/10/2019	65	$        11.42	B65	B
24/10/2019	68	$        17.09	A68	A
24/10/2019	69	$        17.59	B69	B
24/10/2019	70	$        12.34	A70	A
27/10/2019	71	$          2.99	A71	A
27/10/2019	71	$        19.44	B71	B
27/10/2019	73	$          5.41	B73	B
27/10/2019	75	$          3.82	B75	B
27/10/2019	77	$        10.57	A77	A
14/10/2020	1	$          8.55	A1	A
14/10/2020	2	$        19.38	A2	A
14/10/2020	3	$          3.77	B3	B
14/10/2020	3	$        14.97	B3	B
15/10/2020	6	$        16.82	A6	A
15/10/2020	7	$        16.04	A7	A
15/10/2020	8	$          0.42	B8	B
17/10/2020	25	$          6.08	A25	A
18/10/2020	38	$        16.38	A38	A
18/10/2020	39	$        12.19	A39	A
18/10/2020	40	$          5.35	B40	B
18/10/2020	41	$        16.50	B41	B
18/10/2020	42	$        17.14	A42	A
19/10/2020	43	$          9.22	A43	A
19/10/2020	44	$        16.95	B44	B
19/10/2020	45	$        14.92	A45	A
19/10/2020	46	$          0.70	A46	A
19/10/2020	47	$        10.00	B47	B
19/10/2020	48	$        13.62	B48	B
19/10/2020	49	$          0.43	B49	B
20/10/2020	55	$          1.75	A55	A
21/10/2020	61	$          5.54	A61	A
21/10/2020	62	$        16.10	B62	B
21/10/2020	62	$        13.75	B62	B
24/10/2020	64	$        18.23	A64	A
24/10/2020	70	$        15.43	A70	A

And my calendar data

CALENDAR_DATE	C_YEARWEEK	C_YEAR	C_WEEK
14/10/2019	201942	2019	42
15/10/2019	201942	2019	42
16/10/2019	201942	2019	42
17/10/2019	201942	2019	42
18/10/2019	201942	2019	42
19/10/2019	201942	2019	42
20/10/2019	201942	2019	42
21/10/2019	201943	2019	43
22/10/2019	201943	2019	43
23/10/2019	201943	2019	43
24/10/2019	201943	2019	43
25/10/2019	201943	2019	43
26/10/2019	201943	2019	43
27/10/2019	201943	2019	43
14/10/2020	202042	2020	42
15/10/2020	202042	2020	42
16/10/2020	202042	2020	42
17/10/2020	202042	2020	42
18/10/2020	202042	2020	42
19/10/2020	202043	2020	43
20/10/2020	202043	2020	43
21/10/2020	202043	2020	43
22/10/2020	202043	2020	43
23/10/2020	202043	2020	43
24/10/2020	202043	2020	43
25/10/2020	202043	2020	43
26/10/2020	202044	2020	44
27/10/2020	202044	2020	44
28/10/2020	202044	2020	44
29/10/2020	202044	2020	44

And here is the connection in Power BI

Total Sales LY = 
VAR CurrentWeek = SELECTEDVALUE(DateTable[C_WEEK])
VAR CurrentYear = SELECTEDVALUE(DateTable[C_YEAR])
RETURN

SUMX(
  FILTER(ALL(DateTable),
        DateTable[C_WEEK] = CurrentWeek && DateTable[C_YEAR] = CurrentYear -1),
[Total Sales])

But I"m not sure how to replicate for LY Count.

[Anonymous · Accepted solution]

Hi @echow 

I think you have related your sales table and date table by calendar date column.

Firstly add a Yearweek column in SalesTable.

YearWeek = RELATED(DateTable[C_YEARWEEK])

Due to you want to compare values(divide sales with count) in select year with last year, it is better to build a slicer table(Yearweek) which is not related to other tables.

YearWeek = VALUES(DateTable[C_YEARWEEK])

Then you can achieve your goal by measures.

Sales in Bar = 
VAR _SelWeeknum =
    VALUES ( YearWeek[C_YEARWEEK] )
VAR _Sales =
    CALCULATE (
        SUM ( SalesTable[ Sales ] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _SelWeeknum
        )
    )
VAR _Count =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[MBR_NO] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _SelWeeknum
        )
    )
RETURN
    DIVIDE ( _Sales, _Count )

LY Sales in Bar = 
VAR _SelWeeknum =
    ADDCOLUMNS ( VALUES ( YearWeek[C_YEARWEEK] ), "LY Weeknum", [C_YEARWEEK] - 100 )
VAR _LYWeeknum =
    SUMMARIZE ( _SelWeeknum, [LY Weeknum] )
VAR _LYSales =
    CALCULATE (
        SUM ( SalesTable[ Sales ] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _LYWeeknum
        )
    )
VAR _LYCount =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[MBR_NO] ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[Category] = MAX ( SalesTable[Category] )
                && SalesTable[YearWeek] IN _LYWeeknum
        )
    )
RETURN
    DIVIDE ( _LYSales, _LYCount )

Result is as below.

Select 202042 and 202043

Select 202043

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

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

[echow]

HI @Anonymous ,

Could you also share with me the formula for the LY Count for the Sales table?

Rgds,

Eric

[Anonymous]

Hi @echow 

I am so glad to help you solve your problem.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

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

[echow]

Thanks @Anonymous . That is awesome!

[amitchandak]

@echow , for Week on Week, You can create a week rank in date tbale

a new column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

or
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

create measures like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[echow]

@amitchandak Thanks for the reply. I"m not sure how the above will able to solve what I have to the final outcome.