topic Re: Tickets Backlog Calculation through DAX in DAX Commands and Tips

Tickets Backlog Calculation through DAX

rohitchouhan — Mon, 15 Jun 2020 16:40:03 GMT

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

Re: Tickets Backlog Calculation through DAX

mahoneypat — Tue, 16 Jun 2020 02:01:37 GMT

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

Re: Tickets Backlog Calculation through DAX

rohitchouhan — Tue, 16 Jun 2020 09:23:45 GMT

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

Re: Tickets Backlog Calculation through DAX

Anonymous — Tue, 16 Jun 2020 09:55:03 GMT

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)

Re: Tickets Backlog Calculation through DAX

rohitchouhan — Tue, 16 Jun 2020 10:56:46 GMT

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

Re: Tickets Backlog Calculation through DAX

Anonymous — Tue, 16 Jun 2020 16:01:51 GMT

Hi rohitchouhan ,

Assuming that you have a Date Table.

Can you share some sample data and your Data Model

Regards,

Harsh Nathani

Re: Tickets Backlog Calculation through DAX

rohitchouhan — Tue, 16 Jun 2020 16:39:54 GMT

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

Re: Tickets Backlog Calculation through DAX

DiKi-I — Wed, 11 Oct 2023 14:51:16 GMT

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

Re: Tickets Backlog Calculation through DAX

DiKi-I — Sun, 29 Oct 2023 16:10:56 GMT

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