Forum Discussion
Time between two stamps
Fix it with creating a new table and joining it with the current on. All in dax, see formula below :)
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) ) )
Anonymous - OK, I came up with something:
Measure =
VAR __currentHour = HOUR(MAX('Table2'[Hour]))
VAR __startHour = HOUR(MAX('Table1'[Start]))
VAR __endHour = HOUR(MAX('Table1'[End]))
VAR __table = GENERATESERIES(__startHour,__endHour,1)
VAR __table1 = ADDCOLUMNS(__table,"__minutes",
SWITCH(TRUE(),
__startHour < __endHour && [Value] <> __endHour,60,
__startHour < __endHour && [Value] = __endHour,MINUTE(MAX('Table1'[End])),
MINUTE(MAX(Table1[Start]))
)
)
RETURN
MAXX(FILTER(__table1,[Value] = __currentHour),[__minutes])
See Table1 and Table2, Page 1 of attached.
That is exactly what I'm looking for, but when using the code on my data (after changing the values to fit my dataset), it returns the following error:
mdxScript(model)(141,15) Calculation error in measure 'PowerBI_statusupdates'[Time Per Hour'] : The arguments in GenerateSeries function cannot be blank.
I'm using a Date -> Time hierachy in the matrix. From both a Date table and a Time table.
So when the user selects a machine(id), it will show all available dates within the range, with all hours beneath them.
when adding the date to your solution, it gives me the same error
Sounds like you have some blanks in your Start and End times, you'll have to check for that and, if so, probably set the end hour to the start hour I imagine.
I simply added IF BLANK() then 0 to the generateseries function. And then later IF 0 then BLANK() in the return to hide them again. Work perfect now :). Thanks greg for the solution!
Sweet!
The problem seems to be with the Date Table. The date table is based on values from another table until today. The status table only contains data from the last year, but the date table more then 3 years.
Any ideas on how to handle this within the formula? Because I would like to use the date table to filter the report (it contains data from other tables as well, and one date filter would be ideal)