Running Monthly Total Calculation or Filter

[E_Rye]

Good day,

Will someone please help me understand the steps to create a running monthly total that can be filtered by dates/conditions? 

All my data is in one table (Episodes) with one row per unique ID.  Here is a subset:

I have created a measure that calculates a running monthly total (24 months: JAN 2024-JAN2026) for each unique ID, but I am needing to filter out IDs if/when DeliveryDate (or EpisodeEndDate) is outside the ReportingMonth and/or EndOfReportingMonth.

For example, ID 1 should only be included in the running monthly total for 2/1/2024 - 6/1/2024.

I have been unsuccesfull using many different variations of the following logic :

EpisodeStartDate <= EndOfReportingMonth AND DeliveryDate is blank OR DeliveryDate >= ReportingMonth

Thank you very much for any guidance and help!

[ryan_mayu]

@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-de...

[AshokKunwar]

@E_Rye 

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

[FBergamaschi]

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

[krishnakanth240]

Hi @E_Rye 

Create a Date table( please check this post - https://community.fabric.microsoft.com/t5/Desktop/Creating-Date-Tables/m-p/553980) then provide relationship based on date column from Date table to your Main table

Create a measure
Active IDs =
CALCULATE(COUNTROWS(Episodes),FILTER(Episodes,Episodes[Episode Start Date <=MAX(Date[MonthEnd] )&&(
ISBLANK( Episodes[Delivery Date])
|| Episodes[Delivery Date]>=MIN(Date[MonthStart]))))

Cumulative Active IDs =CALCULATE([Active IDs],
FILTER(ALL(Date[Date]),Date[Date]<=MAX( Date[Date])))

[AshokKunwar]

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!

[E_Rye]

Hello @AshokKunwar,

My semantic model already included a Date table not directly linked to the Episodes table, with a calculated column for Reporting Month = STARTOFMONTH('Date'[Date]).

I made one minor change to your logic for 'Active ID Count' to get the correct 'Running Monthly Total':

Active Pregnancy ID Count =

VAR _ReportMonthStart = MAX('Date'[Date])

VAR _ReportMonthStart = MAX('Date'[Reporting Month])

VAR _ReportMonthEnd = EOMONTH(_ReportMonthStart,0)

RETURN

CALCULATE(

    DISTINCTCOUNT('Active Pregnancies By Service Area'[Pregnancy Epic ID]),

    KEEPFILTERS(

        'Active Pregnancies By Service Area'[Episode Start Date (Sorting)] <= _ReportMonthEnd &&

        (ISBLANK('Active Pregnancies By Service Area'[Delivery Date (Sorting)]) || 'Active Pregnancies By Service Area'[Delivery Date (Sorting)] >= _ReportMonthStart

)))

Thank you very much!

[AshokKunwar]

Hii @E_Rye 

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