Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all
I have a specific sitatution where I will see data that is 4 weeks old. And it would often chnage week to week (With the exception of the unique ID)
Then, based on certain criteria, I have to report on this infomration - Could you please give a few suggesitons?
Data:
SnapShot Date | Unique ID | Project | Baseline Date | Slipped | Slipped Amount | Status |
04/05/2023 | 123456 | Build House | 01/05/2023 | Yes | -3 | Ongoing |
11/05/2023 | 123456 | Build House | 08/05/2023 | Yes | -3 | Ongoing |
18/05/2023 | 123456 | Build House | 16/05/2023 | Yes | -2 | Ongoing |
25/05/2023 | 123456 | Build House | 10/06/2023 | No | N/A | Ongoing |
The Slipped is shoeing that the deadline was missed.
Now what I have to do, is Count how often this project 'Slipped in the last 4 Weeks (3)
Then count the total amount of Slipped Days (-8)
Then show this on 1 line on a Dashboard (Probablky Table or Matrix Table)
Considering I have 1000's of entries how can I best resolve this?
Thanks
Solved! Go to Solution.
Hi, @Anonymous
You can try the following methods. Create a new date table.
Table:
Date = CALENDAR(DATE(2023,5,1),DATE(2023,6,30))
Column:
Weeknum = WEEKNUM([Date],2)
Measure:
Count1 = Var _currentweek=WEEKNUM(TODAY())
return
CALCULATE(COUNT('Table'[Slipped]),FILTER(ALL('Table'),[Slipped]="Yes"),FILTER(ALL('Date'),[Weeknum]>=_currentweek-4))
Count2 = Var _currentweek=WEEKNUM(TODAY())
return
CALCULATE(SUM('Table'[Slipped Amount]),FILTER(ALL('Table'),[Slipped]="Yes"),FILTER(ALL('Date'),[Weeknum]>=_currentweek-4))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
You can try the following methods. Create a new date table.
Table:
Date = CALENDAR(DATE(2023,5,1),DATE(2023,6,30))
Column:
Weeknum = WEEKNUM([Date],2)
Measure:
Count1 = Var _currentweek=WEEKNUM(TODAY())
return
CALCULATE(COUNT('Table'[Slipped]),FILTER(ALL('Table'),[Slipped]="Yes"),FILTER(ALL('Date'),[Weeknum]>=_currentweek-4))
Count2 = Var _currentweek=WEEKNUM(TODAY())
return
CALCULATE(SUM('Table'[Slipped Amount]),FILTER(ALL('Table'),[Slipped]="Yes"),FILTER(ALL('Date'),[Weeknum]>=_currentweek-4))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |