Forum Discussion

gauri's avatar
gauri
Helper III
6 years ago
Solved

Problem with cummulative dates in power BI

Hello everyone

 

I'm facing some problem in how to schedule this in Power Bi. Share a screenshot for it.

 

If you check startDTM, ENDDTM, and ENDREASON

so where my COLUMN ENDREASON says Out for that particular time entry is correct, but where it says how lost the time entries, that is, the start time and end time are incorrect. If we consider the first row we have a STARTDTM of 6.30 and ENDDTM of 8.00, so the next entry should take STRTDTM of 8.00 which is, this case is taking as 23.15.

So basically, if my ENDREASON - missedout then the STARTDTM should take the end time of the privious entry and ENDDTM for the lost entry should be 23.15 in this case.

Can someone help me with Dax or with logic that can be implemented in Power BI.

 

Thank you

Gauri

Interesting Links
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi gauri , 

    You could try to create an index column like below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA30TcyVzAwsAIjJR0kQTMrYwwxC7g6/9AQIGmoFKtD0CAjYytDUzyCvp7Bwa4uEAONiDHQAouYoQGa04yVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [APPLYDATE = _t, STARTDTM = _t, ENDDTM = _t, ENDREASON = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"APPLYDATE", type datetime}, {"STARTDTM", type datetime}, {"ENDDTM", type datetime}, {"ENDREASON", type text}, {"ID", Int64.Type}})
in
    #"Changed Type"

     Then try below measure to see whether it work or not

    Measure 4 = var pre=CALCULATE(MIN('Table (2)'[ENDDTM]), FILTER(ALL('Table (2)'), 'Table (2)'[ID]=MIN('Table (2)'[ID])-1)) return  IF(MIN('Table (2)'[ENDREASON])="OUT", (DATEDIFF(MIN('Table (2)'[APPLYDATE]), MIN('Table (2)'[STARTDTM]),MINUTE)-DATEDIFF(MIN('Table (2)'[STARTDTM]),MIN('Table (2)'[ENDDTM]),MINUTE))/60,(DATEDIFF(MIN('Table (2)'[APPLYDATE]), pre,MINUTE)-DATEDIFF(pre,MIN('Table (2)'[ENDDTM]),MINUTE))/60 )

    Best Regards,
    Zoe Zhi

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

4 Replies

  • sanimesa's avatar
    sanimesa
    Post Prodigy
    6 years ago

    gauriCan you please share the formula you are using? It is not clear what data you are starting with.

    • gauri's avatar
      gauri
      Helper III
      6 years ago

      sanimesa  I have not used any measures for the datetime fields, is their any way to correct the existing field

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi gauri , 

    You could try to create an index column like below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA30TcyVzAwsAIjJR0kQTMrYwwxC7g6/9AQIGmoFKtD0CAjYytDUzyCvp7Bwa4uEAONiDHQAouYoQGa04yVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [APPLYDATE = _t, STARTDTM = _t, ENDDTM = _t, ENDREASON = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"APPLYDATE", type datetime}, {"STARTDTM", type datetime}, {"ENDDTM", type datetime}, {"ENDREASON", type text}, {"ID", Int64.Type}})
in
    #"Changed Type"

     Then try below measure to see whether it work or not

    Measure 4 = var pre=CALCULATE(MIN('Table (2)'[ENDDTM]), FILTER(ALL('Table (2)'), 'Table (2)'[ID]=MIN('Table (2)'[ID])-1)) return  IF(MIN('Table (2)'[ENDREASON])="OUT", (DATEDIFF(MIN('Table (2)'[APPLYDATE]), MIN('Table (2)'[STARTDTM]),MINUTE)-DATEDIFF(MIN('Table (2)'[STARTDTM]),MIN('Table (2)'[ENDDTM]),MINUTE))/60,(DATEDIFF(MIN('Table (2)'[APPLYDATE]), pre,MINUTE)-DATEDIFF(pre,MIN('Table (2)'[ENDDTM]),MINUTE))/60 )

    Best Regards,
    Zoe Zhi

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