Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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?
@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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |