Running total countdown

[Friday24]

Hello,

Looking for some help on this. I am trying to create a running countdown but the output is not coming out the way I need. The running countdown is based off of the Issue ID's and an Issue Due Date. The issue ID's are expected to close by a particular due date. I am essentially trying to take the total # of issues and show a projection of that runoff based on the due date, so for example if i have a total # of issues of 7, I want my starting value to be 7, and then for Q2 2024 one of those issues had an expected due date for that year/quarter so the new total of issues expected to be leftover is 6, if in Q3 2024, there are no issues with an expected due date within that quarter then the total would remain 6, in Q4 2024,  2 issues had an expected due date, so the total is now 4, and so forth. 

I utilized the "running total" feature in power BI, but i must be taking the wrong approach, because everytime an expected due date does not have any issues, the total goes back up to 7. below are screenshots of the output and formulas for reference.

The X axis is using "Quarter Dates" and "Year Sort" and the Y axis is using "Current Month Running"

• QuarterDates = IF(ISBLANK('RRP Identified Issues (2)'[Issue Original Due_x]),">",IF('RRP Identified Issues (2)'[Issue Original Due_x] >= DATE(2026,1,1), ">",FORMAT('RRP Identified Issues (2)'[Issue Original Due_x],"\Qtr q")))

The reason for the IF function is to separate any issues with dates not yet identified or past a certain date

• YearSort = IF(ISBLANK('RRP Identified Issues (2)'[Issue Original Due_x]),"Future",IF('RRP Identified Issues (2)'[Issue Original Due_x] >= DATE(2026,1,1), "Future",FORMAT('RRP Identified Issues (2)'[Issue Original Due_x],"yyyy")))

• Current Monthh running total in QuarterDates =

----

CALCULATE(

    [Current Monthh],

    FILTER(

        CALCULATETABLE(

            SUMMARIZE(

                'RRP Identified Issues (2)',

                'RRP Identified Issues (2)'[YearSort],

                'RRP Identified Issues (2)'[QuarterDates]

            ),

            ALLSELECTED('RRP Identified Issues (2)')

        ),

        ISONORAFTER(

            'RRP Identified Issues (2)'[YearSort], MAX('RRP Identified Issues (2)'[YearSort]), asc,

            'RRP Identified Issues (2)'[QuarterDates], Max('RRP Identified Issues (2)'[QuarterDates]), asc

        )

    )

)

• Current Monthh = COUNT('RRP Identified Issues (2)'[Issue ID])

Please Help!

[rajendraongole1 · Accepted solution]

Hi @Friday24 - Hope date table is created in your model,

Create a measure to calculate the total number of issues as below:

Total Issues = COUNT('RRP Identified Issues (2)'[Issue ID])

Create another measure to calculate the number of issues expected to close by each quarter

Issues Closed by Quarter =
CALCULATE(
COUNT('RRP Identified Issues (2)'[Issue ID]),
FILTER(
'RRP Identified Issues (2)',
'RRP Identified Issues (2)'[Issue Original Due_x] <= MAX('Date'[Date])
)
)

for running countdown of issues

Running Countdown =
VAR TotalIssues = [Total Issues]
VAR IssuesClosedToDate =
CALCULATE(
[Issues Closed by Quarter],
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
RETURN
TotalIssues - IssuesClosedToDate

above running countdown starts from the total number of issues and decreases based on the issues over the quarters

Hope the above measure helps to resolve.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result very clearly in a simple Table format.  Share data in a format that can be pasted in an MS Excel file.