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.
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!
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 |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |