Calculate Ticket Backlog in Power BI Desktop

[Asroinfa_user]

Hi All,

I have a report requirement to show a backlog columns as a rolling total of Previous month.

below is the Sample Data .

Month	Open	Closed
7/1/2024	48	42
8/1/2024	34	31
9/1/2024	47	26
10/1/2024	106	82
11/1/2024	189	93
12/1/2024	154	59
1/1/2025	130	59

We can Assume backlog for July 24 is 0. For Aug months Onwards the logic should check the (Open+Backlog)-closed in July month.

Excel formaul: (B2+E2)-C2 to derived backlog for Aug month onwards.

Expected Result That I am looking. 

Month	Open	Closed	Backlog
7/1/2024	48	42	0
8/1/2024	34	31	6
9/1/2024	47	26	9
10/1/2024	106	82	30
11/1/2024	189	93	54
12/1/2024	154	59	150
1/1/2025	130	59	245

[Asroinfa_user]

Thanks it worked. 

[Anonymous]

Hi @Asroinfa_user ,

I am happy to learn that you found the cause of the problem and fixed it.

Please accept danextian's reply as a solution, it will make it easier for other users who may be experiencing the same problem to find a solution.

Thank you for your understanding!

Best regards,

Lucy Chen

[danextian · Accepted solution]

Hi @Asroinfa_user 

The calculation is fairly simple if you use a separate date dimensions table in a one to many relationship to your fact table.

Backlog Start of Month = 
VAR _MinDAte =
    CALCULATE ( MIN ( DatesTbl[Date] ), ALLSELECTED ( DatesTbl ) )
VAR _MaxDate =
    CALCULATE ( MAX ( DatesTbl[Date] ), ALLSELECTED ( DatesTbl ) )
RETURN
    CALCULATE (
        SUM ( Backlog[Open] ) - SUM ( Backlog[Closed] ) + 0,
        FILTER (
            ALL ( DatesTbl ),
            DatesTbl[Date] < MIN ( DatesTbl[Date] )
                && DatesTbl[Date] >= _MinDAte
                && DatesTbl[Date] <= _MaxDate
        )
    )

Filtering by min and max dates is unnecessary. However, if you want to show 0 for instead of blank, it is better to add a condition to limit which rows 0 is added to. Otherwise and if you have dates in your calendar table that are outside the range of your fact table, you will start seeing 0 for those outside the range.

Please refer to the attached pbix for details.