Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I got requirement to calculate time difference in "H:MM" format between two dates with specific rules in 4th column.
Below is sample table format with 3 columns:
And Rules are(as per table data):
1. Need to calculate time when transaction will be with department Dpt001.
2. Time should be calculated within office hours only from 9 Am to 6 PM.
3. Exclude any weekends or public holiday (holiday input will be separate list of holidays (a column in other table) in data model).
4. E.g.-for transaction T01,It will start calculating time from row 1- stop at row 2. start at row 3 -stop at row 5, start at row 7 - calculate till current time. For Tx T002, it will start at row 9- stop at row 10, start at 12-stop at 14
Assuming 9th Oct 2020 is public holiday and currnet date-time is 20 Oct 2020 12:00 PM, I have manually calculated as below (with comments) to make rules more clearer in 4th column 'TimeWithDpt001' for 2 transaction numbers. Also put comments to understnad the calculation:
Row No | TransactionNo | TransactionSentTime | TransactionSentTo | TimeWithDpt001 | Comments for calculations |
1 | T01 | 29/9/2020 11:15 | Dpt001 | Calculation will start from 29 Sep 2020 11:15 AM, as it went to Dpt001 | |
2 | T01 | 2/10/2020 9:30 | Dpt0002 | 25:15 | before sent to Dpt0002, Time with Dpt001 is 25 hours 30 minutes (only time between 9 to 6 calculation) |
3 | T01 | 7/10/2020 15:45 | Dpt001 | Time start for Dpt001. | |
4 | T01 | 10/10/2020 10:05 | Dpt001 | Assuming 9 Oct is public holiday and 10, 11 oct is weeked, these 3 days will not be considered for calculation TimeWith Dpt001 | |
5 | T01 | 13/10/2020 12:15 | Dpt0002 | 23:30 | once it sent to Dpt0002,calculation stops. before sent to Dpt0002, Time with Dpt001 is 23 hours 30 minutes |
6 | T01 | 14/10/2020 13:25 | Dpt0002 | ignore calculation as it is stillwith Dpt0002 | |
7 | T01 | 15/10/2020 8:45 | Dpt001 | 30:00 | Calculation start from 15Oct 9AMnot from 8:45AM due to work hours) and will continue till current date-time(20 Oct 2020 12:00 PM). skip weekend(17, 18 Oct) |
8 | T002 | 15/9/2020 7:55 | Dpt0002 | no calculation as transaction starting with Dpt0002 | |
9 | T002 | 22/9/2020 18:13 | Dpt001 | Time calculation will NOT starts on 22 Sep 2020 at 6:13 PM as work hours end at 6 PM. It starts on 23 Sep 2020 9AM. | |
10 | T002 | 10/10/2020 0:45 | Dpt0002 | 108:00 | It will calculate till 8 Oct 2020 6PM as 9 Oct is public holiday and 10 Oct is weekend. |
11 | T002 | 10/10/2020 18:15 | Dpt0002 | skip | |
12 | T002 | 11/10/2020 13:10 | Dpt001 | Start time calculation from 12 OCT 2020 9 AM as 11 Oct is Sunday | |
13 | T002 | 15/10/2020 11:25 | Dpt001 | continue calculation | |
14 | T002 | 16/10/2020 12:50 | Dpt0002 | 39:50 | Calculation ends once transaction sent to Dpt0002 |
Anyone can please help me to suggest DAX/calculated column /power query to achieve the same? I tried out multiple DAX functions but nothing giving desired outcome. any hints /tips are welcome.
Thanks in Advance
Solved! Go to Solution.
Hi @Anonymous ,
I made it !😁 Please check and any other methods please advise.
1. Create a date column and then create relationship between your TransactionLog table and your dates table based on the date column.
2. Modified TransactionSentTime.
ModifiedSentTime =
SWITCH (
[TransactionSentTo],
"Dpt001",
SWITCH (
TRUE (),
RELATED ( 'Calendar'[WeekDay] ) = 7,
RELATED ( 'Calendar'[Date] ) + 1
+ TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) = 6,
RELATED ( 'Calendar'[Date] ) + 2
+ TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) = 5
&& [TransactionSentTime]
>= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[Date] ) + 3
+ TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4, 5 }
&& [TransactionSentTime]
<= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ), RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4 }
&& [TransactionSentTime]
>= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[Date] ) + 1
+ TIME ( 9, 0, 0 ),
[TransactionSentTime]
),
"Dpt0002",
SWITCH (
TRUE (),
RELATED ( 'Calendar'[WeekDay] ) = 7,
RELATED ( 'Calendar'[Date] ) - 2
+ TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) = 6,
RELATED ( 'Calendar'[Date] ) - 1
+ TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) = 1
&& [TransactionSentTime]
<= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[Date] ) - 3
+ TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) IN { 2, 3, 4, 5 }
&& [TransactionSentTime]
<= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[Date] ) - 1
+ TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4, 5 }
&& [TransactionSentTime]
>= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ), RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
[TransactionSentTime]
)
)
3. Create TimeWithDpt001 column.
TimeWithDpt001 column =
VAR t =
FILTER ( TransactionLog, [TransactionNo] = EARLIER ( [TransactionNo] ) )
VAR PreviousDpt0002No =
MAXX (
FILTER ( t, [Row No] < EARLIER ( [Row No] ) && [TransactionSentTo] = "Dpt0002" ),
[Row No]
)
VAR LastDpt0002No =
MINX (
FILTER (
t,
[Row No] <= EARLIER ( [Row No] )
&& [Row No] > PreviousDpt0002No
&& [TransactionSentTo] = "Dpt0002"
),
[Row No]
)
VAR FirstRowNo =
CALCULATE (
FIRSTNONBLANK ( TransactionLog[Row No], 1 ),
FILTER (
TransactionLog,
TransactionLog[TransactionNo] = EARLIER ( TransactionLog[TransactionNo] )
)
)
VAR LastRowNo =
CALCULATE (
LASTNONBLANK ( TransactionLog[Row No], 1 ),
FILTER (
TransactionLog,
TransactionLog[TransactionNo] = EARLIER ( TransactionLog[TransactionNo] )
)
)
VAR StartDateTime_ =
IF (
[Row No] = FirstRowNo
&& [TransactionSentTo] = "Dpt0002",
BLANK (),
MINX (
FILTER ( t, [TransactionSentTo] = "Dpt001" && [Row No] > PreviousDpt0002No ),
[ModifiedSentTime]
)
)
VAR ModifiedNow =
SWITCH (
TRUE (),
WEEKDAY ( TODAY () ) = 7,
TODAY () - 2
+ TIME ( 18, 0, 0 ),
WEEKDAY ( TODAY () ) = 6,
TODAY () - 1
+ TIME ( 18, 0, 0 ),
WEEKDAY ( TODAY () ) = 1
&& NOW ()
<= TODAY () + TIME ( 9, 0, 0 ),
TODAY () - 3
+ TIME ( 18, 0, 0 ),
WEEKDAY ( TODAY () ) IN { 2, 3, 4, 5 }
&& NOW ()
<= TODAY () + TIME ( 9, 0, 0 ),
TODAY () - 1
+ TIME ( 18, 0, 0 ),
WEEKDAY ( TODAY () ) IN { 1, 2, 3, 4, 5 }
&& NOW ()
>= TODAY () + TIME ( 18, 0, 0 ), TODAY () + TIME ( 18, 0, 0 ),
NOW ()
)
VAR EndDateTime_ =
IF (
[Row No] = FirstRowNo
&& [TransactionSentTo] = "Dpt0002",
BLANK (),
MINX (
FILTER ( t, [TransactionSentTo] = "Dpt0002" && [Row No] > PreviousDpt0002No ),
[ModifiedSentTime]
)
)
VAR EndDateTime_2 =
IF (
[Row No] = LastRowNo
&& [TransactionSentTo] = "Dpt001",
ModifiedNow,
IF (
[Row No] = LastRowNo
&& [TransactionSentTo] = "Dpt0002",
[ModifiedSentTime],
EndDateTime_
)
)
VAR DateDiff_m =
DATEDIFF ( StartDateTime_, EndDateTime_2, MINUTE )
VAR RestDayCount =
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
'Calendar',
'Calendar'[Date] >= StartDateTime_
&& 'Calendar'[Date] <= EndDateTime_2
&& (
[WeekDay] IN { 6, 7 }
|| [Holiday(just for test)] <> BLANK ()
)
)
) + 0
VAR DayDiff =
DATEDIFF ( StartDateTime_, EndDateTime_2, DAY )
VAR ModifiedDiff = DateDiff_m - RestDayCount * 9 * 60 - DayDiff * 15 * 60
VAR HHMM =
IF (
ModifiedDiff = BLANK (),
BLANK (),
INT ( ModifiedDiff / 60 ) & ":"
& FORMAT ( MOD ( ModifiedDiff, 60 ), "00" )
)
RETURN
IF (
[Row No] = LastRowNo
|| ( NOT ( LastDpt0002No = PreviousDpt0002No + 1 )
&& [Row No] = LastDpt0002No ),
HHMM
)
Best regards
Icey
If this post helps,then consider Accepting it as the solution to help other members find it faster.
@Icey I was reading your solution and I am looking for a similar solution, but through your proposed code it is not working. I have mentioned the post link here Could you please help me out with it?
@Icey - The logics looks great. I have a similar use case, where I need to calculate only the days difference instead hours calculation.
I have been reading your code for the past three to four days. As a beginner to PowerBi, I couldn't decode and understand all of your DAX measures, some codes yes.
Can you please help me understand your codes - By adding comments to each line of codes? I will be thankful to you lot.
Raja from India
Hi @Anonymous ,
I made it !😁 Please check and any other methods please advise.
1. Create a date column and then create relationship between your TransactionLog table and your dates table based on the date column.
2. Modified TransactionSentTime.
ModifiedSentTime =
SWITCH (
[TransactionSentTo],
"Dpt001",
SWITCH (
TRUE (),
RELATED ( 'Calendar'[WeekDay] ) = 7,
RELATED ( 'Calendar'[Date] ) + 1
+ TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) = 6,
RELATED ( 'Calendar'[Date] ) + 2
+ TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) = 5
&& [TransactionSentTime]
>= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[Date] ) + 3
+ TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4, 5 }
&& [TransactionSentTime]
<= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ), RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4 }
&& [TransactionSentTime]
>= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[Date] ) + 1
+ TIME ( 9, 0, 0 ),
[TransactionSentTime]
),
"Dpt0002",
SWITCH (
TRUE (),
RELATED ( 'Calendar'[WeekDay] ) = 7,
RELATED ( 'Calendar'[Date] ) - 2
+ TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) = 6,
RELATED ( 'Calendar'[Date] ) - 1
+ TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) = 1
&& [TransactionSentTime]
<= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[Date] ) - 3
+ TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) IN { 2, 3, 4, 5 }
&& [TransactionSentTime]
<= RELATED ( 'Calendar'[Date] ) + TIME ( 9, 0, 0 ),
RELATED ( 'Calendar'[Date] ) - 1
+ TIME ( 18, 0, 0 ),
RELATED ( 'Calendar'[WeekDay] ) IN { 1, 2, 3, 4, 5 }
&& [TransactionSentTime]
>= RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ), RELATED ( 'Calendar'[Date] ) + TIME ( 18, 0, 0 ),
[TransactionSentTime]
)
)
3. Create TimeWithDpt001 column.
TimeWithDpt001 column =
VAR t =
FILTER ( TransactionLog, [TransactionNo] = EARLIER ( [TransactionNo] ) )
VAR PreviousDpt0002No =
MAXX (
FILTER ( t, [Row No] < EARLIER ( [Row No] ) && [TransactionSentTo] = "Dpt0002" ),
[Row No]
)
VAR LastDpt0002No =
MINX (
FILTER (
t,
[Row No] <= EARLIER ( [Row No] )
&& [Row No] > PreviousDpt0002No
&& [TransactionSentTo] = "Dpt0002"
),
[Row No]
)
VAR FirstRowNo =
CALCULATE (
FIRSTNONBLANK ( TransactionLog[Row No], 1 ),
FILTER (
TransactionLog,
TransactionLog[TransactionNo] = EARLIER ( TransactionLog[TransactionNo] )
)
)
VAR LastRowNo =
CALCULATE (
LASTNONBLANK ( TransactionLog[Row No], 1 ),
FILTER (
TransactionLog,
TransactionLog[TransactionNo] = EARLIER ( TransactionLog[TransactionNo] )
)
)
VAR StartDateTime_ =
IF (
[Row No] = FirstRowNo
&& [TransactionSentTo] = "Dpt0002",
BLANK (),
MINX (
FILTER ( t, [TransactionSentTo] = "Dpt001" && [Row No] > PreviousDpt0002No ),
[ModifiedSentTime]
)
)
VAR ModifiedNow =
SWITCH (
TRUE (),
WEEKDAY ( TODAY () ) = 7,
TODAY () - 2
+ TIME ( 18, 0, 0 ),
WEEKDAY ( TODAY () ) = 6,
TODAY () - 1
+ TIME ( 18, 0, 0 ),
WEEKDAY ( TODAY () ) = 1
&& NOW ()
<= TODAY () + TIME ( 9, 0, 0 ),
TODAY () - 3
+ TIME ( 18, 0, 0 ),
WEEKDAY ( TODAY () ) IN { 2, 3, 4, 5 }
&& NOW ()
<= TODAY () + TIME ( 9, 0, 0 ),
TODAY () - 1
+ TIME ( 18, 0, 0 ),
WEEKDAY ( TODAY () ) IN { 1, 2, 3, 4, 5 }
&& NOW ()
>= TODAY () + TIME ( 18, 0, 0 ), TODAY () + TIME ( 18, 0, 0 ),
NOW ()
)
VAR EndDateTime_ =
IF (
[Row No] = FirstRowNo
&& [TransactionSentTo] = "Dpt0002",
BLANK (),
MINX (
FILTER ( t, [TransactionSentTo] = "Dpt0002" && [Row No] > PreviousDpt0002No ),
[ModifiedSentTime]
)
)
VAR EndDateTime_2 =
IF (
[Row No] = LastRowNo
&& [TransactionSentTo] = "Dpt001",
ModifiedNow,
IF (
[Row No] = LastRowNo
&& [TransactionSentTo] = "Dpt0002",
[ModifiedSentTime],
EndDateTime_
)
)
VAR DateDiff_m =
DATEDIFF ( StartDateTime_, EndDateTime_2, MINUTE )
VAR RestDayCount =
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
'Calendar',
'Calendar'[Date] >= StartDateTime_
&& 'Calendar'[Date] <= EndDateTime_2
&& (
[WeekDay] IN { 6, 7 }
|| [Holiday(just for test)] <> BLANK ()
)
)
) + 0
VAR DayDiff =
DATEDIFF ( StartDateTime_, EndDateTime_2, DAY )
VAR ModifiedDiff = DateDiff_m - RestDayCount * 9 * 60 - DayDiff * 15 * 60
VAR HHMM =
IF (
ModifiedDiff = BLANK (),
BLANK (),
INT ( ModifiedDiff / 60 ) & ":"
& FORMAT ( MOD ( ModifiedDiff, 60 ), "00" )
)
RETURN
IF (
[Row No] = LastRowNo
|| ( NOT ( LastDpt0002No = PreviousDpt0002No + 1 )
&& [Row No] = LastDpt0002No ),
HHMM
)
Best regards
Icey
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hello @Icey , Many thanks for reverting back with solution. this is great.
and my apologies for delay response on the same.
Logic looks promising. I was trying to implement the same and got wrong calculated time for only some of rows. When checked, I noticed ModifiedSentTime is not working in my case. column is NOT giving any error.
For SWITCH statement , it is always calculating 'False' for every condition and returning [TransactionSentTime] Always. Also it is actually not bringing values from Calendar table (either date or weekDay). I checked the relationship it is fine.
I also tried to convert SWITCH to IF statement but same issue while comparing.
Can you please guide what may be wrong here?
Hi ALL, @Icey
by making both columns data type strictly 'Date' type and format 'd/m/yyyy', it got resolved
Thanks
Hello @Anonymous
What would be the final format? Would this be in Excel or Power BI?
Also, can you edit the data beforehand? Add columns or reorganize it before importing it into Power Query/DAX etc.?
Hi @BlueTeam1 , Thanks for your comment.
And sorry I missed some information.
This data is getting imported from excel into Power BI. Yes, we can edit data, add /reorganize columns in excel and power BI both but final column (4th column ) should come in PowerBI Query/Desktop.
Format of the 4th Column should be as 'H:MM' format i.e '3:30' will represent 3 hours 30 minutes OR '23:12' will represent 23hours 12minutes.
But even we can calculate it only in 'minutes' (as integer) such as 3:30 can be calculated as 210 (in minutes). that also should be fine as later I can convert to required format.
Thanks in Advance,
Komal