The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I Understand what I am asking may require setting up a new table in Power Bi. Example of current Data below.
My goal is to determine the minutes offline and online for each individual day. As you can see my Start and End data times are logged. Using Row 1 as an example I need a new row with all data copied and create with start time as 2/9/18 12:00 AM and original row Endtime changed to 2/8/18 11:59.59 PM The Minutes Offline and Online recalculated to reflect accurately.
Please note that end date can be multiple days out, I need each row to be individual days.
Hoping this make sense and someone can help me.
RoomName | RoomCategory | DeviceName | DeviceType | StartTime | StartValue | EndTime | EndValue | MinutesOffline | StartHours | StartDate | EndDate | EndHours | Percent | MinutesOnline |
O3 | RoomA | 760 | A | 2/8/18 2:10 PM | 2 | 2/9/18 1:15 PM | 2 | 1385 | 14:10:12 | 2/8/2018 | 2/9/2018 | 1:15:02 PM | 96.18% | 55 |
O3 | RoomA | 760 | A | 2/7/18 1:36 PM | 2 | 2/8/18 2:10 PM | 2 | 1474 | 13:36:11 | 2/7/2018 | 2/8/2018 | 2:10:12 PM | 102.36% | -34 |
O3 | RoomA | 760 | A | 2/6/18 3:06 PM | 2 | 2/7/18 1:36 PM | 2 | 1350 | 15:06:17 | 2/6/2018 | 2/7/2018 | 1:36:11 PM | 93.75% | 90 |
O3 | RoomA | 760 | A | 2/5/18 2:30 PM | 2 | 2/6/18 3:06 PM | 2 | 1476 | 14:30:22 | 2/5/2018 | 2/6/2018 | 3:06:17 PM | 102.50% | -36 |
O3 | RoomA | 760 | A | 2/1/18 7:28 PM | 2 | 2/5/18 2:30 PM | 2 | 5462 | 19:28:47 | 2/1/2018 | 2/5/2018 | 2:30:22 PM | 379.31% | -4022 |
O3-3 | Conference | GS4 | A | 1/30/18 3:23 PM | 2 | 1/30/18 4:08 PM | 2 | 45 | 15:23:02 | 1/30/2018 | 1/30/2018 | 4:08:58 PM | 3.13% | 1395 |
O3-3 | Conference | GS4 | A | 1/30/18 3:12 PM | 2 | 1/30/18 3:23 PM | 2 | 11 | 15:12:41 | 1/30/2018 | 1/30/2018 | 3:23:02 PM | 0.76% | 1429 |
O3-3 | Conference | GS4 | A | 1/30/18 1:36 PM | 2 | 1/30/18 3:12 PM | 2 | 96 | 13:36:53 | 1/30/2018 | 1/30/2018 | 3:12:41 PM | 6.67% | 1344 |
O3-3 | Conference | GS4 | A | 1/29/18 9:49 PM | 2 | 1/30/18 1:36 PM | 2 | 947 | 21:49:36 | 1/29/2018 | 1/30/2018 | 1:36:53 PM | 65.76% | 493 |
O3-3 | Conference | GS4 | A | 1/29/18 9:00 PM | 2 | 1/29/18 9:49 PM | 2 | 49 | 21:00:34 | 1/29/2018 | 1/29/2018 | 9:49:36 PM | 3.40% | 1391 |
Solved! Go to Solution.
Hi @rtillery2000,
Please check out the demo here.
1. Add a custom column like this.
{Number.From([StartDate])..Number.From([EndDate])}
2. Expand the custom column.
3. Change its type to Date. (not datetime).
4. Add a new column "NewStart".
if ( [Temp] = [StartDate]) then [StartTime] else [Temp]
5. Change its type to datetime.
6. Add a new column "NewEnd".
if ([Temp] = [EndDate]) then [EndTime] else [Temp] & #time(23,59,59)
7. You can delete the old two columns.
Best Regards,
Dale
Hi @rtillery2000,
Please check out the demo here.
1. Add a custom column like this.
{Number.From([StartDate])..Number.From([EndDate])}
2. Expand the custom column.
3. Change its type to Date. (not datetime).
4. Add a new column "NewStart".
if ( [Temp] = [StartDate]) then [StartTime] else [Temp]
5. Change its type to datetime.
6. Add a new column "NewEnd".
if ([Temp] = [EndDate]) then [EndTime] else [Temp] & #time(23,59,59)
7. You can delete the old two columns.
Best Regards,
Dale
Hi
I'am looking for an almost similar solution.
Contract Position from Date to Date over an periode (multiple years)
Split the contract positon amount over all month with creating multiple rows.
any ideas - help would be great!
thx Reto
Solution with an array per year | |||||||||||||||||||||
Contract-Nr. | Pos1 | Betrag | Jahr | von | bis | Koar | Anzahl M | Anzahl J | amount/m | m1 | m2 | m3 | m4 | m5 | m6 | m7 | m8 | m9 | m10 | m11 | m12 |
5001 | 5000.0001 | 1000 | 2017 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | ||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 |
5001 | 5000.0001 | 1000 | 2019 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | 41.67 | ||||||
5001 | 5000.0002 | 2500 | 2017 | 01.07.2017 | 30.06.2019 | 475000 | 24 | 3 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | ||||||
5001 | 5000.0002 | 2500 | 2018 | 01.07.2017 | 30.06.2019 | 475000 | 24 | 3 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 |
5001 | 5000.0002 | 2500 | 2019 | 01.07.2017 | 30.06.2019 | 475000 | 24 | 3 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | 104.17 | ||||||
5001 | 5000.0003 | 1500 | 2017 | 01.07.2017 | 30.06.2019 | 550000 | 24 | 3 | 62.50 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | ||||||
5001 | 5000.0003 | 1500 | 2018 | 01.07.2017 | 30.06.2019 | 550000 | 24 | 3 | 62.50 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 |
5001 | 5000.0003 | 1500 | 2019 | 01.07.2017 | 30.06.2019 | 550000 | 24 | 3 | 62.50 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | 62.5 | ||||||
OR BETTER: FOR EACH MONTH ONE ROW | Month | ||||||||||||||||||||
5001 | 5000.0001 | 1000 | 2017 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 7.2017 | |||||||||||
5001 | 5000.0001 | 1000 | 2017 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 8.2017 | |||||||||||
5001 | 5000.0001 | 1000 | 2017 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 9.2017 | |||||||||||
5001 | 5000.0001 | 1000 | 2017 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 10.2017 | |||||||||||
5001 | 5000.0001 | 1000 | 2017 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 11.2017 | |||||||||||
5001 | 5000.0001 | 1000 | 2017 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 12.2017 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 1.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 2.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 3.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 4.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 5.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 6.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 7.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 8.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 9.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 10.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 11.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 12.2018 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 1.2019 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 2.2019 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 3.2019 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 4.2019 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 5.2019 | |||||||||||
5001 | 5000.0001 | 1000 | 2018 | 01.07.2017 | 30.06.2019 | 455500 | 24 | 3 | 41.67 | 6.2019 |
Thank you, that was much easier than the route I was headed down.