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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gonzalis
New Member

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

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi @Gonzalis 

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:

1.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-lili6-msft
Community Support
Community Support

hi @Gonzalis 

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:

1.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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