Forum Discussion

Yiannis_N's avatar
Yiannis_N
Frequent Visitor
2 years ago
Solved

DAX formula for Monthly Cumulative Backlog Ticket Calculation

Hello, i have found a response in regards of the calculation of Monthly backlog and I m trying to implement a similar measure on my Power BI reports and i think the method described of solving the p...
Backlog
Calculated Measures
DAX
power bi
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Yiannis_N 

     

    First, you need a column to calculate Open_Tickets minus Close_Tickets:

    Column = [Opened_Tickets] - [Closed_Tickets]

     

    Then you can use the following dax to get the result you want:

    Measure = 
var select_date = SELECTEDVALUE('Table'[Year])
RETURN
SELECTEDVALUE('Table'[Column])+SUMX(FILTER(ALL('Table'),'Table'[Year]<select_date),'Table'[Column])

     

    Result:

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Merc_A's avatar
Merc_A
Frequent Visitor
11 months ago

Very late to the party here But This issue took a long time for me to solve myself. I put together a Proof PBX file so i wouldn't forget. My initial use case was identical to yours (ticket volume) however my Proof I put together is a more universal example. Historical Employee Headcount. Since both examples provide only a key, start date & end date, the math is the same. 

PBX Proof

Here are the measures in the proof and their use case. 

1) (Your use case) This counts all active Employees / Tickets over the time period defined by your X-Axis (Yr, Qtr, Mth). 
Employee Count During Period = 
Var EndDatePerVisual = MAX('Calendar'[Dates])
VAR StartdatePerVisual = MIN('Calendar'[Dates])
Var RESULT = 
CALCULATE(
    COUNTROWS('Employee Data'),
    REMOVEFILTERS('Calendar'),
    'Employee Data'[Start Date] <= EndDatePerVisual,
    'Employee Data'[End Date] > StartdatePerVisual
    || 
    ISBLANK('Employee Data'[End Date])
)
RETURN
RESULT

 


2) Same as before, but instead of counting all Active items over the month, This one returns a snapshot of the counts as they were at the end of the time period (yr, qtr, month).

Employee Count at End of Period = 
Var EndDatePerVisual = MAX('Calendar'[Dates])
Var RESULT = 
CALCULATE(
    COUNTROWS('Employee Data'),
    REMOVEFILTERS('Calendar'),
    'Employee Data'[Start Date] <= EndDatePerVisual,
    'Employee Data'[End Date] > EndDatePerVisual
    || 
    ISBLANK('Employee Data'[End Date])
)
RETURN
RESULT