Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have data roughly in the below format. For each week, I want to get the count of IDs that were present in the previous week but are missing in the current week. In the example below, April 14th - 2 (IDs 2 and 4 are missing), April 21st - 1 (ID 5 is missing). I want to create a chart with this week over week numbers.
id | Week |
1 | 4/7/2023 |
2 | 4/7/2023 |
3 | 4/7/2023 |
4 | 4/7/2023 |
1 | 4/14/2023 |
3 | 4/14/2023 |
5 | 4/14/2023 |
6 | 4/14/2023 |
1 | 4/21/2023 |
3 | 4/21/2023 |
6 | 4/21/2023 |
Please let me know how I can go about it. Note that I have other columns in the table and may need to apply some filters in the formula like name = "xyz" while doing this calculation.
Thank you!
Solved! Go to Solution.
Missing =
VAR __wk = MAX( STAT[week] )
RETURN
TOCSV(
EXCEPT(
CALCULATETABLE( VALUES( STAT[id] ), STAT[week] = __wk - 7 ),
VALUES( STAT[id] )
),
,
,
FALSE()
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
Missing =
VAR __wk = MAX( STAT[week] )
RETURN
TOCSV(
EXCEPT(
CALCULATETABLE( VALUES( STAT[id] ), STAT[week] = __wk - 7 ),
VALUES( STAT[id] )
),
,
,
FALSE()
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Thank you @ThxAlot ! I used COUNTROWS instead of TOCSV to get the count instead of the list. Appreciate your help!
User | Count |
---|---|
101 | |
90 | |
83 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |