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 Ou...
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.