March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have a data set witch contains every movement a patient does in the hospital.
Every movement has it's own row.
The row has an start and end date so you can determine where the patient was for every day. But how 🙂
Reference | start date | end date | department |
12345 | 1-1-2022 10:00 | 1-1-2022 15:00 | a |
12345 | 1-1-2022 15:00 | 1-1-2022 16:00 | b |
9842 | 31-1-2022 09:35 | 12-2-2022 15:34 | a |
6587 | 2-1-2022 3:25 | 3-1-2022 16:35 | a |
I want a calculation for every day (and for every hour) how many patients where present
Expected outcome for department a:
Date | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 |
1-1-2022 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
2-1-2022 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
The problem is that 1 reference can be present several times so the count will not be correct when I count the rows.
I hope somebody can help me.
Solved! Go to Solution.
Hi @bdeleur
Please refer to attached sample file with the solution
Count =
VAR CurrentDate = SELECTEDVALUE ( 'Date'[Date] )
VAR CurrentHour = SELECTEDVALUE ( Hours[Hour] )
RETURN
SUMX (
VALUES ( 'Table'[department] ),
VAR CurrentDepartmentTable = CALCULATETABLE ( 'Table' )
VAR FilteredTable =
FILTER (
CurrentDepartmentTable,
VAR StartDate = 'Table'[start date]
VAR EndDate = 'Table'[end date]
RETURN
CurrentHour
IN SELECTCOLUMNS (
GENERATESERIES ( MAX ( CurrentDate, StartDate ), MIN ( CurrentDate + 0.99, EndDate ), 1/24 ),
"@Hour", HOUR ( [Value] )
)
)
RETURN
COUNTROWS ( FilteredTable )
)
Max Count =
MAXX (
CROSSJOIN (
VALUES ( 'Date'[Date] ),
VALUES ( Hours[Hour] )
),
[Count]
)
Min Count =
MINX (
CROSSJOIN (
VALUES ( 'Date'[Date] ),
VALUES ( Hours[Hour] )
),
[Count]
)
Average Count =
AVERAGEX (
CROSSJOIN (
VALUES ( 'Date'[Date] ),
VALUES ( Hours[Hour] )
),
[Count]
)
Hi @bdeleur
Please refer to attached sample file with the solution
Count =
VAR CurrentDate = SELECTEDVALUE ( 'Date'[Date] )
VAR CurrentHour = SELECTEDVALUE ( Hours[Hour] )
RETURN
SUMX (
VALUES ( 'Table'[department] ),
VAR CurrentDepartmentTable = CALCULATETABLE ( 'Table' )
VAR FilteredTable =
FILTER (
CurrentDepartmentTable,
VAR StartDate = 'Table'[start date]
VAR EndDate = 'Table'[end date]
RETURN
CurrentHour
IN SELECTCOLUMNS (
GENERATESERIES ( MAX ( CurrentDate, StartDate ), MIN ( CurrentDate + 0.99, EndDate ), 1/24 ),
"@Hour", HOUR ( [Value] )
)
)
RETURN
COUNTROWS ( FilteredTable )
)
Max Count =
MAXX (
CROSSJOIN (
VALUES ( 'Date'[Date] ),
VALUES ( Hours[Hour] )
),
[Count]
)
Min Count =
MINX (
CROSSJOIN (
VALUES ( 'Date'[Date] ),
VALUES ( Hours[Hour] )
),
[Count]
)
Average Count =
AVERAGEX (
CROSSJOIN (
VALUES ( 'Date'[Date] ),
VALUES ( Hours[Hour] )
),
[Count]
)
Hi @tamerj1
Works perfect. Thank you!
I also want a min, max and average for the whole day. Is that also possible with your code?
Super!
Thank you very much. I'm very happy.
Beta tumse na ho payega.
Don't know what you mean. But the result is like this in Excel:
The data looks like this and contains a column Ward:
The formula I use in excel is:
AANTAL.ALS($B:$B;"<"&($K7+AL$6))-AANTAL.ALS($C:$C;"<"&($K7+AL$6))
Column B is the start date en C is the End date
Aantal.als = count.if in English.
In excel I use this formula:
Can you please provide more data regarding the output. Didn't understand the required output properly.
Thank you.
Hi @VishalJhaveri,
Tnx for looking at my problem. I want for every day and every hour a count how many patients there are in a ward. This is an example for ward A for 2 days but it continues for the whole year and with a slicer for other wards also.
Makes it more clear now?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
29 | |
20 | |
15 | |
14 | |
10 |
User | Count |
---|---|
62 | |
25 | |
24 | |
22 | |
16 |