Forum Discussion

bdeleur's avatar
bdeleur
Icon for Helper III rankHelper III
3 years ago
Solved

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:

Date01234567891011121314151617181920212223
1-1-2022111111111122222111111111
2-1-2022111222222222222222222222

 

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.

Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    • bdeleur's avatar
      bdeleur
      Icon for Helper III rankHelper III
      3 years ago

      Hi Anonymous,

       

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Beta tumse na ho payega.

    • bdeleur's avatar
      bdeleur
      Icon for Helper III rankHelper III
      3 years ago

       

      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: 

       

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

     

    • bdeleur's avatar
      bdeleur
      Icon for Helper III rankHelper III
      3 years ago

      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?