Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
raicardi
Frequent Visitor

Calculate overlapping shift start

‎08-08-2022 04:29 PM

I am relatively new to BI, more used to excel, and am having a difficult time showing overlapping shift. Formula and screenshot of excel below. 

F(F3=F2, IF(H3=I2, "follow", IF(H3<I2, "OVERLAP", "After")), "---")

 

raicardi_0-1660001287870.png

Any help would be greatly appreciated. 

Labels:
Message 1 of 12
1,033 Views
0
1 ACCEPTED SOLUTION
Bubble4502
Resolver III
Resolver III

‎08-10-2022 11:29 PM

Hi @raicardi ,

 

Approve with @MahyarTF .

Here is my solution:

You can create index column in Power Query:

Bubble4502_0-1660198112416.png

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEytLC01DU00jU2UDAwtzIwACJUUUNTqGisDi6NhsZWxpgajQyhohCNRuTaiE0jUTYak2sjNo2EbIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Date" = _t, #"In Time" = _t, #"Out Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work Date", type date}, {"In Time", type datetime}, {"Out Time", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Then create and apply the measure:

Measure = 
var _a = MAX('Table'[Index])
var _b = FILTER(ALL('Table'),[Index]=_a-1)
return IF( MAXX(_b,'Table'[Work Date])=MAX('Table'[Work Date]),
SWITCH(TRUE(),
MAXX(_b,[Out Time])=MAX('Table'[In Time]),"follow",
MAXX(_b,[Out Time])>MAX('Table'[In Time]),"OVERLAP",
"After"),"---")

Output:

Bubble4502_1-1660199387780.png

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 11 of 12
910 Views
11 REPLIES 11
v-jianboli-msft
Community Support
Community Support

‎08-17-2022 10:37 PM

Hi @raicardi ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 12 of 12
874 Views
0
Bubble4502
Resolver III
Resolver III

‎08-10-2022 11:29 PM

Hi @raicardi ,

 

Approve with @MahyarTF .

Here is my solution:

You can create index column in Power Query:

Bubble4502_0-1660198112416.png

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEytLC01DU00jU2UDAwtzIwACJUUUNTqGisDi6NhsZWxpgajQyhohCNRuTaiE0jUTYak2sjNo2EbIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Date" = _t, #"In Time" = _t, #"Out Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work Date", type date}, {"In Time", type datetime}, {"Out Time", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Then create and apply the measure:

Measure = 
var _a = MAX('Table'[Index])
var _b = FILTER(ALL('Table'),[Index]=_a-1)
return IF( MAXX(_b,'Table'[Work Date])=MAX('Table'[Work Date]),
SWITCH(TRUE(),
MAXX(_b,[Out Time])=MAX('Table'[In Time]),"follow",
MAXX(_b,[Out Time])>MAX('Table'[In Time]),"OVERLAP",
"After"),"---")

Output:

Bubble4502_1-1660199387780.png

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 11 of 12
911 Views
MahyarTF
Memorable Member
Memorable Member

‎08-08-2022 06:01 PM

Hi,

- I create the column as an Index Column :

DAX Index =
RANKX(
    ALL( Sheet72 ), --Table Name
    CONCATENATE( Sheet72[Word Day], format( Sheet72[In Time], "hh:mm:ss") ),,
    ASC,
    Dense
)
- then create another Column to calculate the particular column :
Previous Quantity =
VAR _CurrentRowIndex = Sheet72[DAX Index]  
VAR _PreviousRowDate = CALCULATE(format(MAX( Sheet72[Word Day]), "dd/mm/yyy") ,
                                FILTER( Sheet72, Sheet72[DAX Index] < _CurrentRowIndex ),
                                ALL( Sheet72[DAX Index] )                                )
Var _CurrentRowDate = format (CALCULATE( max(Sheet72[Word Day])), "dd/mm/yyy")
/*VAR _PreviousRowInTime = CALCULATE(format(MAX( Sheet72[In Time]), "hh:mm:ss") , FILTER( Sheet72, Sheet72[DAX Index] < _CurrentRowIndex ), ALL( Sheet72[DAX Index] ) ) */
Var _CurrentRowInTime = format (CALCULATE( max(Sheet72[In Time])), "hh:mm:ss")
VAR _PreviousRowOutTime = CALCULATE(format(MAX( Sheet72[Out Time]), "hh:mm:ss") ,
                                    FILTER( Sheet72, Sheet72[DAX Index] = _CurrentRowIndex-1 ),
                                    ALL( Sheet72[DAX Index] )                                 )
--Var _CurrentRowOutTime = format (CALCULATE( max(Sheet72[Out Time])), "hh:mm:ss")
Var _result = if(_PreviousRowDate = _CurrentRowDate,
                 if(_CurrentRowInTime = _PreviousRowOutTime, "follow",
                    if(_CurrentRowInTime < _PreviousRowOutTime, "OVERLAP", "After")
                    )
                 ,"---"
                )
RETURN 
  _Result
- Now this is the result Table visual :
MahyarTF_0-1660006908892.png

 

Mahyartf
Message 2 of 12
981 Views
raicardi
Frequent Visitor
In response to MahyarTF

‎08-09-2022 03:46 PM

Thanks for the infomation. I realized i needed to group 20 distinct locations so the formula above was helpful  but am stil unable to achieve the result im looing for. Columns and screenshots below. [DAX Index] below is the 

 

Overlap = VAR _CurrentRowIndex = Sheet1[DAX Index]  
VAR _PreviousRowDate = CALCULATE(format(MAX( Sheet1[Work Date]), "dd/mm/yyy") ,
                                FILTER( Sheet1, Sheet1[DAX Index] < _CurrentRowIndex ),
                                ALL( Sheet1[DAX Index] )                                )
Var _CurrentRowDate = format (CALCULATE( max(Sheet1[Work Date])), "dd/mm/yyy")
/*VAR _PreviousRowInTime = CALCULATE(format(MAX( Sheet1[In Time]), "hh:mm:ss") , FILTER( Sheet1, Sheet1[DAX Index] < _CurrentRowIndex ), ALL( Sheet1[DAX Index] ) ) */
Var _CurrentRowInTime = format (CALCULATE( max(Sheet1[In Time])), "hh:mm:ss")
VAR _PreviousRowOutTime = CALCULATE(format(MAX( Sheet1[Out Time]), "hh:mm:ss") ,
                                    FILTER( Sheet1, Sheet1[DAX Index] = _CurrentRowIndex-1 ),
                                    ALL( Sheet1[DAX Index] )                                 )
--Var _CurrentRowOutTime = format (CALCULATE( max(Sheet1[Out Time])), "hh:mm:ss")
Var _result = if(_PreviousRowDate = _CurrentRowDate,
                 if(_CurrentRowInTime >= _PreviousRowOutTime, "follow",
                    if(_CurrentRowInTime <= _PreviousRowOutTime, "OVERLAP", "After")
                    )
                 ,"---"
                )
RETURN
  _Result
 
Results below are after group by indexes added for each of the 20 groups. 
raicardi_0-1660085030560.png

 

Message 3 of 12
965 Views
0
MahyarTF
Memorable Member
Memorable Member
In response to raicardi

‎08-09-2022 05:07 PM

Not sure, why it is not working, 

Would you please share your sample PBIX file ?

I check my doing again and it is working 

MahyarTF_0-1660090032893.png

 

Mahyartf
Message 4 of 12
951 Views
raicardi
Frequent Visitor
In response to MahyarTF

‎08-10-2022 03:08 PM

Let me know if this works?

Work DateIn TimeOut TimeOverlapOrder 
1/1/20221899-12-30 07:00:001899-12-30 15:00:00'---1 
1/1/20221899-12-30 13:30:001899-12-30 21:30:00'---2 
1/2/20221899-12-30 07:00:001899-12-30 15:00:00'---3 
1/2/20221899-12-30 13:30:001899-12-30 21:30:00'---4 
1/3/20221899-12-30 07:00:001899-12-30 15:00:00'---5 
1/3/20221899-12-30 13:30:001899-12-30 21:30:00'---6 
1/4/20221899-12-30 07:00:001899-12-30 21:30:00'---7 
1/5/20221899-12-30 07:00:001899-12-30 21:30:00'---8 
1/6/20221899-12-30 07:00:001899-12-30 21:30:00'---9 
1/7/20221899-12-30 07:00:001899-12-30 15:00:00'---10 
1/7/20221899-12-30 13:30:001899-12-30 21:30:00'---11 
1/8/20221899-12-30 07:00:001899-12-30 21:30:00'---12 
1/9/20221899-12-30 07:00:001899-12-30 15:00:00OVERLAP13 
1/9/20221899-12-30 13:30:001899-12-30 21:30:00OVERLAP14 
########1899-12-30 07:00:001899-12-30 15:00:00'---15 
########1899-12-30 13:30:001899-12-30 21:30:00'---16 
########1899-12-30 07:00:001899-12-30 21:30:00'---17 
########1899-12-30 07:00:001899-12-30 15:00:00'---18 
########1899-12-30 13:30:001899-12-30 21:30:00'---19 
########1899-12-30 07:00:001899-12-30 15:00:00'---20 
########1899-12-30 13:30:001899-12-30 21:30:00'---21 
########1899-12-30 07:00:001899-12-30 21:30:00'---22 
########1899-12-30 07:00:001899-12-30 15:00:00'---23 
########1899-12-30 13:30:001899-12-30 21:30:00'---24 
########1899-12-30 07:00:001899-12-30 21:30:00'---25 
########1899-12-30 07:00:001899-12-30 15:00:00'---26 
########1899-12-30 13:30:001899-12-30 21:30:00'---27 
########1899-12-30 07:00:001899-12-30 21:30:00'---28 
########1899-12-30 07:00:001899-12-30 15:00:00'---29 
########1899-12-30 13:30:001899-12-30 21:30:00'---30 
########1899-12-30 07:00:001899-12-30 21:30:00'---31 
########1899-12-30 07:00:001899-12-30 15:00:00'---32 
########1899-12-30 13:30:001899-12-30 21:30:00'---33 
########1899-12-30 07:00:001899-12-30 21:30:00'---34 
########1899-12-30 07:00:001899-12-30 21:30:00'---35 
########1899-12-30 07:00:001899-12-30 15:00:00'---36 
########1899-12-30 13:30:001899-12-30 21:30:00'---37 
########1899-12-30 07:00:001899-12-30 21:30:00'---38 
########1899-12-30 07:00:001899-12-30 21:30:00'---39 
########1899-12-30 07:00:001899-12-30 21:30:00'---40 
########1899-12-30 07:00:001899-12-30 15:00:00'---41 
########1899-12-30 13:30:001899-12-30 21:30:00'---42 
########1899-12-30 07:00:001899-12-30 15:00:00'---43 
########1899-12-30 13:30:001899-12-30 21:30:00'---44 
########1899-12-30 07:00:001899-12-30 21:30:00'---45 
########1899-12-30 15:00:001899-12-30 21:30:00'---46 
########1899-12-30 07:00:001899-12-30 15:00:00'---47 

 

 

Message 5 of 12
943 Views
0
MahyarTF
Memorable Member
Memorable Member
In response to raicardi

‎08-10-2022 05:09 PM

Hi,

This is my result : 

MahyarTF_0-1660176507173.pngMahyarTF_1-1660176575895.png

 

Mahyartf
Message 6 of 12
933 Views
raicardi
Frequent Visitor
In response to MahyarTF

‎08-10-2022 08:52 PM

thanks. Still not sure what the issue is. I've included the entire data set with all 20 groups, each with their own corresponding index. Hoping this is my issue..

 

Data 

Message 7 of 12
928 Views
0
MahyarTF
Memorable Member
Memorable Member
In response to raicardi

‎08-10-2022 09:05 PM

I hope so,

Please Kudos my posts, if those are help you

Mahyartf
Message 8 of 12
927 Views
raicardi
Frequent Visitor
In response to MahyarTF

‎08-10-2022 09:17 PM

Do you think it's related to multiple indexes? If you could take a look at the data link below that would be amazing. 

https://docs.google.com/spreadsheets/d/162cWchQU-t_Gc7o4wgUH4WXyzCa1Dhq4J3BLDjHbMZg/edit?usp=sharing

Message 9 of 12
924 Views
0
MahyarTF
Memorable Member
Memorable Member
In response to raicardi

‎08-10-2022 09:46 PM

I think so, please use my dax for creating the Index column

Mahyartf
Message 10 of 12
916 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All