Forum Discussion

mithunt's avatar
mithunt
Frequent Visitor
1 year ago
Solved

Help with cascade formula

Hello PowerBians, 

 

I am trying to achive open ticket calculation based on logic attached with this question,

However it is extremly difficult in Power BI, where as in Excel its just drag the formula.

 

Here is how the calculation I need

 

I have date, Created tickets, Resolved tickets and Open ticket(Latest Date)

Based on latest open tickets I am trying to reverse map the previous Open tickets

 

Yesterdays' open = Latest Open-Latest Created+Latest Resolved 

 

Even though entries below are dummy but the ones marked in yellow are entries which I get and the rest should be calculated as per logic above

 

 

 

This logic should be moved up, so that I get open tickets for any given date.

 

Requesting your expertise. 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi mithunt 

     

    Given the logic provided, 8/21 shoud return 219 (220-43+44) but yours is 221. Following the same logic, this calc column should work.

    Open2 = 
VAR _currentDate = Data[Date]
VAR _open =
    SUM ( Data[Open] ) --assuming there's only one open date data
VAR created_resolved =
    SUMX (
        FILTER ( Data, Data[Date] > _currentDate ),
        - Data[Created] + Data[Resolved]
    )
RETURN
    _open + created_resolved

    As a measure

    Open measure = 
VAR _open =
    SUMX ( ALL ( Data[Date] ), CALCULATE ( SUM ( Data[Open] ) ) ) --assuming there's only one open date data
VAR _currentDate =
    MAX ( Data[Date] )
VAR created_resolved =
    SUMX (
        FILTER ( ALL ( Data ), Data[Date] > _currentDate ),
        - Data[Created] + Data[Resolved]
    )
RETURN
    _open + created_resolved

     

5 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi mithunt 

     

    Given the logic provided, 8/21 shoud return 219 (220-43+44) but yours is 221. Following the same logic, this calc column should work.

    Open2 = 
VAR _currentDate = Data[Date]
VAR _open =
    SUM ( Data[Open] ) --assuming there's only one open date data
VAR created_resolved =
    SUMX (
        FILTER ( Data, Data[Date] > _currentDate ),
        - Data[Created] + Data[Resolved]
    )
RETURN
    _open + created_resolved

    As a measure

    Open measure = 
VAR _open =
    SUMX ( ALL ( Data[Date] ), CALCULATE ( SUM ( Data[Open] ) ) ) --assuming there's only one open date data
VAR _currentDate =
    MAX ( Data[Date] )
VAR created_resolved =
    SUMX (
        FILTER ( ALL ( Data ), Data[Date] > _currentDate ),
        - Data[Created] + Data[Resolved]
    )
RETURN
    _open + created_resolved

     

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    Hi mithunt,

    Checking in to see if your issue has been resolved. let us know if you still need any assistance.

     

    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    11 months ago

    Hi mithunt,

    We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

     

    Thank you.