Grouping and count (On conditions & variables)

[Anonymous]

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 

[v-zhangti · Accepted 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.