Tickets Backlog Calculation through DAX

[rohitchouhan]

Based on the first 3 columns(Image Attached),  I am calculating Backlog for each day in excel, now I am moving to Power BI and new here.
Using DAX how can I calculate Backlog from the first 3 columns in Power BI.
I have Incident ID, Created Date and Closed Date column , and I want Backlog of each day.
Kindly suggest
 

Thankyou

[DiKi-I]

@rohitchouhan Do you have the solution, can you share it with me?

[DiKi-I]

Can you please help me with this we you have the solution?

[harshnathani]

Hi @rohitchouhan ,

 Backlog =
var ClosedTckts = CALCULATE(COUNT('Table'[Close Date]),USERELATIONSHIP('Date'[Date],'Table'[Close Date]))

var OpenTckts= CALCULATE(COUNT('Table'[Open Date]),USERELATIONSHIP('Date'[Date],'Table'[Open Date]))

var Difference = ClosedTckts - OpenTckts

var Cumulative = FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date]))

var Backlog = CALCULATE(
SUMX('Date', Difference),
Cumulative)

return
Backlog

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[rohitchouhan]

@harshnathani  Hello sir, thanks for reply
what to to put here in place of ('Date'[Date], 
kindly help

[harshnathani]

Hi @rohitchouhan ,

Assuming that you have a Date Table.

Can you share some sample data and your Data Model

Regards,

Harsh Nathani

[rohitchouhan]

I don't have a date table there,I just have created a date and closed date for tickets and a unique Incident ID.

My data source is excel here, which i am sharing here.

 Click to open file 

[mahoneypat]

Assuming you have a Date table (e.g., with your Date Index from visual), you could try an expression like this to get the # of open items on any given day (in a table visual with your 'Date'[Date] column).

Open Incidents =
VAR __thisdate =
    MIN ( 'Date'[Date] ) //assuming you have one date in context min, max, sum won't matter
RETURN
    CALCULATE (
        COUNTROWS ( Table ),
        ALL ( Table ),
        Table[Created Date] <= __thisdate,
        Table[Closed Date] > __thisdate
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[rohitchouhan]

@mahoneypat  this is not working.
my input data is first 3 column and result I want is the last column(Backlog).
also can you temm how to add date index through DAX