Forum Discussion

rohitchouhan's avatar
rohitchouhan
Frequent Visitor
6 years ago

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 column...
Backlog
DAX
power bi
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    rohitchouhan
    Frequent Visitor
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi rohitchouhan ,

       

      Assuming that you have a Date Table.

       

      Can you share some sample data and your Data Model

       

      Regards,

      Harsh Nathani

      • rohitchouhan's avatar
        rohitchouhan
        Frequent Visitor
        6 years ago

        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