cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Time between two stamps

OK, can you provide some sample data which exhibits the problem and what the output *should* be? Pretty sure the formula can be adapted. I also published it as a Quick Measure with a few updates. Not sure if the updates would solve the issue:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
AndreLap Regular Visitor
Regular Visitor

Re: Time between two stamps

Fix it with creating a new table and joining it with the current on. All in dax, see formula below Smiley Happy
Might not be the fastest with creating a fer variables, I still need to learn how to do it, but it gets the job done perfectly!

PresenceGrouping = 
VAR __datetable = 
        GENERATESERIES(
            MROUND(MIN(PowerBI_Status[Starttime + Date])-1/24, "1:00"), NOW()+1/24, TIME(1,0,0))

VAR __datetable1 = 
        ADDCOLUMNS(__datetable, "_endtime", [Value] + 1/24)

VAR __table2 = 
        FILTER(
        CROSSJOIN(__datetable1, PowerBI_Status),
            [_endtime] >= PowerBI_Status[Starttime + Date] && [Value] <= PowerBI_Status[Endtime + Date])

VAR __table3 = 
        SELECTCOLUMNS(__table2, [columns here, to much to show])

RETURN
    ADDCOLUMNS(__table3, "Seconds",
                SWITCH(
                    TRUE(),
                    [PresenceStart] < [Start] && [PresenceEnd] > [end], 3600,
                    [PresenceStart] < [Start] && [PresenceEnd] <= [end], DATEDIFF([Start], [PresenceEnd], SECOND),
                    [PresenceStart] >= [Start] && [PresenceEnd] > [end], DATEDIFF([PresenceStart], [end], SECOND),
                    DATEDIFF([PresenceStart], [PresenceEnd], SECOND)
                )
    )