Running Monthly Total Calculation or Filter

Hii E_Rye 

​In your data, an ID has a "Life Cycle" that starts at EpisodeStartDate and ends at DeliveryDate (or EpisodeEndDate). A standard running total often ignores the end date, causing IDs to stay in the count forever.

​The Solution: The "Active ID" Pattern

​Step 1: Set up a Disconnected Date Table

​For dynamic filtering to work across a 24-month trend, your X-axis should come from a separate Calendar table that is not directly linked to your Episodes dates. This allows the measure to compare the "Month being viewed" against the "Dates in the row."

​Step 2: Create the "Active ID" Logic

​Create a measure that determines if an ID is active during a given month. Use the logic you described: EpisodeStartDate must be on or before the end of the month, and the ID must not have been "Delivered" before the start of that month.

Active ID Count = 
VAR _ReportMonthStart = MAX('Calendar'[Date])
VAR _ReportMonthEnd = EOMONTH(_ReportMonthStart, 0)

RETURN
CALCULATE(
    DISTINCTCOUNT(Episodes[ID]),
    KEEPFILTERS(
        Episodes[EpisodeStartDate] <= _ReportMonthEnd && 
        (ISBLANK(Episodes[DeliveryDate]) || Episodes[DeliveryDate] >= _ReportMonthStart)
    )
)

Step 3: Create the Running Total

​Now, wrap that "Active" logic into a running total calculation. This will iterate through all months up to the one currently being viewed in your chart.

Running Monthly Total = 
VAR _MaxDate = MAX('Calendar'[Date])

RETURN
CALCULATE(
    [Active ID Count],
    FILTER(
        ALLSELECTED('Calendar'),
        'Calendar'[Date] <= _MaxDate
    )
)

Alternative: Using a Visual-Level Filter

​If you want to keep your existing simple count measure, you can create a "Flag" measure:

1. ​Create a measure: FilterFlag = IF([Active ID Count] > 0, 1, 0).
2. ​Drag this into the Filters on this visual pane for your chart.
3. ​Set the filter to "is 1".

If this solution worked for you, please Mark as Solution! It helps others in the community find this answer more easily. Cheers!

Hi krishnakanth240,

the general pattern is something link this

Active IDs =
VAR MaxDate = MAX(Date[MonthEnd] )
VAR MinDate = MIN ( Date[MonthEnd] )
RETURN
CALCULATE(
           COUNTROWS(Episodes),

           ( Episodes[Episode Start Date <=MaxDate && ISBLANK( Episodes[Delivery Date]) ) ||
           Episodes[Delivery Date]>=MinDate,
          REMOVEFILTERS ( Episodes )
)

Cumul Active IDs =
VAR MaxDate = MAX(Date[MonthEnd] )
RETURN
CALCULATE (
             [Active IDs],
             Date[Date]<=MaxDate
)

But to be sure to provide the righe answer, I need to know how you arrange the matrix (what you have in rows, columns, etc)

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

E_Rye 

If this solution worked for you, please Mark as Solution! It helps others in the community find this answer more easily. Cheers!

E_Rye 

you can create a calculated column

StatusFlag =
IF(
Table[EpisodeStartDate] <= Table[EndOfReportingMonth]
&&
(
ISBLANK(Episodes[DeliveryDate])
||
Table[DeliveryDate] >= Table[ReportingMonth]
),
"y", 
)

then add the calculated column to filters , slicers, visual filter or page filter

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter?tabs=powerbi-desktop?wt.mc_id=DP-MVP-5004616

Hi E_Rye  ,

That’s good to know, thank you for confirming. Your adjustment makes sense.

If you need any more information or clarification, please let us know.

Thank you for your reply, AshokKunwar .

Regards,
Yugandhar

Hi E_Rye ,

Everything looks clear from our side. Please let us know if you need any additional information or clarification. We’re happy to assist further if needed.

Thank you.