How to show same period last year data based on group by distinct count data

[echow]

Basically I need help to create the column (LY) - Last year based on the same week. (Highlighted in Red)

The DAX for TY is

TY = Calculate( Distinctcount (Sales[MBR_NO]),

        Groupby(Sales, Sales[Category], Sales[YearWeek]))

How do I obtain for the LY column so that it will appear as the table below.

Note: The blank row in between 201947 and 202042 is to show that there should be continuation of the remaining year week.

[echow]

Previously I have attempted the above problem with this code, but it appear as blank on the LY which I suspect it's because I don't have a function like the SUMX function.

Member Weekly Count Per Sku LY =

VAR CurrentWeek = SELECTEDVALUE(DATE_TABLE[C_WEEK])

VAR CurrentYear = SELECTEDVALUE(DATE_TABLE[C_YEAR])

Var MaxWeekNumber = CALCULATE(max(DATE_TABLE[C_WEEK]),all(DATE_TABLE[CALENDAR_DATE]))

RETURN

CALCULATE(DISTINCTCOUNT(SALES[MBR_NO]),

GROUPBY(SALES, SALES[CATEGORY], DATE_TABLE[C_YEARWEEK]),

FILTER(all(DATE_TABLE),

DATE_TABLE[C_WEEK] = CurrentWeek && DATE_TABLE[C_YEAR] = CurrentYear - 1

))

For the SUMX function it works, can I do something similar for distinct count as well?

Sales LY =

VAR CurrentWeek = SELECTEDVALUE(DATE_TABLE[C_WEEK])

VAR CurrentYear = SELECTEDVALUE(DATE_TABLE[C_YEAR])

Var MaxWeekNumber = CALCULATE(max(DATE_TABLE[C_WEEK]),all(DATE_TABLE[CALENDAR_DATE]))

RETURN

SUMX(

FILTER(all(DATE_TABLE),

DATE_TABLE[C_WEEK] = CurrentWeek && DATE_TABLE[C_YEAR] = CurrentYear - 1),

[Sales])

[v-shex-msft]

Hi @echow,
Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

Regards,
Xiaoxin Sheng

[echow]

@v-shex-msft , I have just added a dummy dataset and the outcome. 

[v-shex-msft · Accepted solution]

Hi @echow,

You can refer to the following steps to achieve your requirements:

1. Create a new table with category and index, then use index fields as the 'sort by column' of type fields.

Category = 
DATATABLE ( "Type", STRING, "Index", INTEGER, { { "TY", 1 }, { "LY", 2 } } )

2. Write a measure with switch function to calculate based on current categories.

Measure = 
VAR currType =
    SELECTEDVALUE ( 'Category'[Type] )
VAR currYW =
    MAX ( Sale[YearWeeK] )
RETURN
    SWITCH (
        currType,
        "TY",
            CALCULATE (
                SUM ( Sale[MBR_NO] ),
                ALLSELECTED ( 'Sale' ),
                VALUES ( Sale[Category] ),
                VALUES ( 'Sale'[YearWeeK] )
            ),
        "LY",
            CALCULATE (
                SUM ( Sale[MBR_NO] ),
                FILTER (
                    ALLSELECTED ( 'Sale' ),
                    'Sale'[YearWeeK]
                        = ( LEFT ( currYW, 4 ) - 1 ) * 100
                            + RIGHT ( currYW, 2 )
                ),
                VALUES ( Sale[Category] )
            )
    )

3. Build matrix visual based on the raw table, new table fields, and use measure on value field.

Regards,

Xiaoxin Sheng

[echow]

Sales Table

CALENDAR_DATE	MBR_NO	Item	Category	YearWeeK
14/10/2019	1	A1	A	201942
14/10/2019	3	B3	B	201942
14/10/2019	3	B3	B	201942
14/10/2019	4	A4	A	201942
14/10/2019	5	A5	A	201942
15/10/2019	6	A6	A	201942
15/10/2019	7	A7	A	201942
15/10/2019	9	A9	A	201942
15/10/2019	13	A13	A	201942
15/10/2019	14	B14	B	201942
15/10/2019	14	B14	B	201942
16/10/2019	18	A18	A	201942
16/10/2019	18	A18	A	201942
16/10/2019	21	A21	A	201942
17/10/2019	22	A22	A	201942
17/10/2019	23	B23	B	201942
17/10/2019	24	B24	B	201942
17/10/2019	33	B33	B	201942
18/10/2019	36	B36	B	201942
18/10/2019	37	B37	B	201942
18/10/2019	40	A40	A	201942
18/10/2019	40	A40	A	201942
18/10/2019	40	B40	B	201942
18/10/2019	41	B41	B	201942
18/10/2019	42	A42	A	201942
19/10/2019	43	A43	A	201942
19/10/2019	44	B44	B	201942
19/10/2019	44	A44	A	201942
20/10/2019	51	A51	A	201942
20/10/2019	52	B52	B	201942
20/10/2019	55	A55	A	201942
20/10/2019	56	A56	A	201942
21/10/2019	57	B57	B	201943
21/10/2019	57	B57	B	201943
21/10/2019	57	B57	B	201943
21/10/2019	60	B60	B	201943
21/10/2019	63	B63	B	201943
24/10/2019	64	A64	A	201943
24/10/2019	65	A65	A	201943
24/10/2019	65	B65	B	201943
24/10/2019	68	A68	A	201943
24/10/2019	69	B69	B	201943
24/10/2019	70	A70	A	201943
27/10/2019	71	A71	A	201943
27/10/2019	71	B71	B	201943
27/10/2019	73	B73	B	201943
27/10/2019	75	B75	B	201943
27/10/2019	77	A77	A	201943
14/10/2020	1	A1	A	202042
14/10/2020	2	A2	A	202042
14/10/2020	3	B3	B	202042
14/10/2020	3	B3	B	202042
15/10/2020	6	A6	A	202042
15/10/2020	7	A7	A	202042
15/10/2020	8	B8	B	202042
17/10/2020	25	A25	A	202042
18/10/2020	38	A38	A	202042
18/10/2020	39	A39	A	202042
18/10/2020	40	B40	B	202042
18/10/2020	41	B41	B	202042
18/10/2020	42	A42	A	202042
19/10/2020	43	A43	A	202043
19/10/2020	44	B44	B	202043
19/10/2020	45	A45	A	202043
19/10/2020	46	A46	A	202043
19/10/2020	47	B47	B	202043
19/10/2020	48	B48	B	202043
19/10/2020	49	B49	B	202043
20/10/2020	55	A55	A	202043
21/10/2020	61	A61	A	202043
21/10/2020	62	B62	B	202043
21/10/2020	62	B62	B	202043
24/10/2020	64	A64	A	202043
24/10/2020	70	A70	A	202043

Date Table

CALENDAR_DATE	C_YEARWEEK
10/10/2019	201941
11/10/2019	201941
12/10/2019	201941
13/10/2019	201941
14/10/2019	201942
15/10/2019	201942
16/10/2019	201942
17/10/2019	201942
18/10/2019	201942
19/10/2019	201942
20/10/2019	201942
21/10/2019	201943
22/10/2019	201943
23/10/2019	201943
24/10/2019	201943
25/10/2019	201943
26/10/2019	201943
27/10/2019	201943
8/10/2020	202041
9/10/2020	202041
10/10/2020	202041
11/10/2020	202041
12/10/2020	202042
13/10/2020	202042
14/10/2020	202042
15/10/2020	202042
16/10/2020	202042
17/10/2020	202042
18/10/2020	202042
19/10/2020	202043
20/10/2020	202043
21/10/2020	202043
22/10/2020	202043
23/10/2020	202043
24/10/2020	202043
25/10/2020	202043

Outcome

	TY	TY	LY	LY
Category	A	B	A	B
201942	17	11
201943	6	8
202042	8	4	17	11
202043	7	5	6	8

TY = Calculate( Distinctcount (Sales[MBR_NO]),

        Groupby(Sales, Sales[Category], Sales[YearWeek]))

How do I get LY?

[amitchandak]

@echow , for week intelligence you can rank your year week . You have to use a separate week/date table

a new column

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

and try measure like

This Week = CALCULATE(Distinctcount ('sales'[MBR_NO]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(Distinctcount ('sales'[MBR_NO]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(Distinctcount ('sales'[MBR_NO]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(Distinctcount ('sales'[MBR_NO]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

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

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[echow]

My Sales table is actually connected to a calendar table. I have just posted another code above which probably mimic to what you have, but I probably need a DISTINCTCOUNTX function, which does not exist.