Forum Discussion
Tickets Backlog Calculation through DAX
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
8 Replies
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
BacklogRegards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Anonymous Hello sir, thanks for reply
what to to put here in place of ('Date'[Date],
kindly helpHi rohitchouhan ,
Assuming that you have a Date Table.
Can you share some sample data and your Data Model
Regards,
Harsh Nathani
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
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
Can you please help me with this we you have the solution?
rohitchouhan Do you have the solution, can you share it with me?
