Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to measure our salary cost by 15 min intervals.
Now we are only getting it by the day.
My current data looks like this:
Name | Start | End | Minutes | Salary / Minute | Total Salary |
Amanda | 31-01-2019 10:00 | 31-01-2019 20:00 | 600 | 1,67 | 1.000 |
Eduardo | 31-01-2019 12:00 | 31-01-2019 22:47 | 647 | 1,67 | 1.078 |
Clara | 31-01-2019 12:30 | 31-01-2019 20:15 | 465 | 2,00 | 930 |
Gabriel | 31-01-2019 16:00 | 31-01-2019 22:46 | 406 | 1,67 | 677 |
Sofia | 31-01-2019 17:00 | 31-01-2019 22:38 | 338 | 1,83 | 620 |
Jane | 31-01-2019 18:00 | 31-01-2019 22:33 | 333 | 1,67 | 555 |
John | 31-01-2019 18:00 | 31-01-2019 22:32 | 272 | 2,00 | 544 |
and the desired output looks like this:
Time | Salary |
17:00 | 132,50 |
17:15 | 132,50 |
17:30 | 132,50 |
17:45 | 132,50 |
18:00 | 187,50 |
18:15 | 187,50 |
18:30 | 187,50 |
18:45 | 187,50 |
Can anybody help with accomplishing this?
HI @ThomasBachmann,
I'd like to suggest you create time interval table and bridge table with with name and time range, then use name field to link original table, time range to link time interval table.
Time interval Table = SELECTCOLUMNS ( GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 59 ), TIME ( 0, 15, 0 ) ), "Time", [Value] )
Bridge =
SELECTCOLUMNS (
FILTER (
CROSSJOIN ( Table, 'Time Interval' ),
[Time] >= TIMEVALUE ( [Start] )
&& [Time] <= TIMEVALUE ( [End] )
),
"Name", Table[Name],
"Time Range", [Time]
)
After these, you can simply create graph with time interval table and original table fields.
Notice: It will generate huge amount of records with each time interval range defined by each row start end time.
Regards,
Xiaoxin Sheng
The solution does not work when we have a shift that stretches across two days.
Ex.
Start
15-02-2019 17:00:00
End
16-02-2019 01:00:00
How to resolve this issue?
Hi @ThomasBachmann,
You can cross join date part with your records before expand time table, then add condition to check both date and time values.
BTW, I'm not so suggest you to expand date and time at same time.(it will generate huge amount of records)
Regards,
Xiaoxin Sheng
I have now created two crossjoined tables.
Workplan 1 =
FILTER (
CROSSJOIN ( Workplan; 'Date Table' );
'Date Table'[Dates] >= Workplan[Date.Start]
&& 'Date Table'[Dates] <= Workplan[Date.End]
)
--
Workplan 2 =
FILTER (
CROSSJOIN ( 'Workplan 1'; 'Time Table' );
'Time Table'[Time] >= TIMEVALUE('Workplan 1'[Start])
&& 'Time Table'[Time] <= TIMEVALUE('Workplan 1'[End])
)
However it is still not giving me shifts that cross two dates?
Hi @ThomasBachmann ,
If you expand both date and time part, bridge table will generate too many records.(it will effect performance)
In my opinion, I'd like to suggest you extract two table with date and time part, then you can build visuals through date/time dimensions.
Regards,
Xiaoxin Sheng
Thank you very much, it worked
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |