Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
58 | |
21 | |
18 | |
16 | |
12 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |