Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Acumulative inputs and outputs through time

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

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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