Help with cascade formula

[mithunt]

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. 

[v-saisrao-msft]

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.

[v-saisrao-msft]

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]

Hi @mithunt,

Have you had a chance to review the solution we shared by @danextian @Greg_Deckler ? If the issue persists, feel free to reply so we can help further.

Thank you

[danextian · Accepted solution]

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

[Greg_Deckler]

@mithunt Sounds like you want Open Tickets: Open Tickets - Microsoft Fabric Community