Acumulative inputs and outputs through time

[Anonymous]

I have some data containing ID#, Initiated DateTime Stamp, Finished DataTime Stamp. I also have a DateTime Calendar dimension which is related to the first table toward Initiated TimeStamp.

For example:
ID#     Initiated Stamp          Finished Stamp
125     29/10/2019 08:00      29/10/2019 12:00
129     29/10/2019 09:00      29/10/2019 14:00
135     29/10/2019 10:00      29/10/2019 15:00
138     29/10/2019 12:00      29/10/2019 13:00

Expected result: how many simultaneously ID# there were though time. Step by Step this is how it should count

@29/10/2019
08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00
   1        1        1        1       1        0        0       0
   0        1        1        1       1        1        1       0
   0        0        1        1       1        1        1       1
   0        0        0        0       1        1        0       0

In this example, I should get a visual containing

@29/10/2019

08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00
    1       2        2        2        3       3        2       1

This is the measure I've tried, but didn't work as I thoght:

ID# Simultaneously = CALCULATE(COUNTROWS(FactTable);FILTER(FactTable;FactTable[Initiated Stamp]>=RELATED('dCalendar'[DateTime]) || FactTable[Finished Stamp] <= RELATED('dCalendar'[Date])))

[v-lili6-msft · Accepted solution]

hi @Anonymous 

For your case, you could try this formula as below:

Step1:

Create a date table with 24 datetimes per day, you could try this formula:

dCalendar = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( MIN(FactTable[Initiated Stamp]), MAX(FactTable[Finished Stamp])),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 8, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)

Step2:

Don't create the relationship between fact table and this Calendar table.

Step3:

Create a measure as below:

Measure = 
CALCULATE (
    COUNT ( FactTable[ID# ]),
    FILTER (
        FactTable,
        FactTable[Initiated Stamp] <= SELECTEDVALUE(dCalendar[DateTime]) 
            &&  FactTable[Finished Stamp]  >=  SELECTEDVALUE( dCalendar[DateTime])
))

Result:

and here is sample pbix file, please try it.

Regards,

Lin