Forum Discussion

raicardi's avatar
raicardi
Frequent Visitor
4 years ago
Solved

Calculate overlapping shift start

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")), "---")

 

Any help would be greatly appreciated. 

Need Help
  • Bubble4502's avatar
    Bubble4502
    4 years ago

    Hi raicardi ,

     

    Approve with MahyarTF .

    Here is my solution:

    You can create index column in Power Query:

    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:

    Kind Regards,

    Bubble

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

11 Replies

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    4 years ago

    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 :

     

    • raicardi's avatar
      raicardi
      Frequent Visitor
      4 years ago

      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. 

       

      • MahyarTF's avatar
        MahyarTF
        Memorable Member
        4 years ago

        Not sure, why it is not working, 

        Would you please share your sample PBIX file ?

        I check my doing again and it is working 

         

  • Bubble4502's avatar
    Bubble4502
    Resolver III
    4 years ago

    Hi raicardi ,

     

    Approve with MahyarTF .

    Here is my solution:

    You can create index column in Power Query:

    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:

    Kind Regards,

    Bubble

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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    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.