Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

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.

Helper II

## Count for every day from startdate and enddate

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.

1 ACCEPTED SOLUTION
Super User

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]
)``````

8 REPLIES 8
Super User

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]
)``````

Helper II

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 User

@bdeleur
Please refer to my original reply updated as required.

Helper II

Super!

Thank you very much. I'm very happy.

Resolver III

Beta tumse na ho payega.

Helper II

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:

Resolver III

Can you please provide more data regarding the output. Didn't understand the required output properly.
Thank you.

Helper II

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?

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors