Running total scenarios - filter/exclude entries based on combination of 2 columns

[MEverts]

Background: I have built a patient-recruitment forecast-tool for a clinical-study, where a running total (of number patients per clinical-site/country) is plotted per calendar day. Source material is a list of hospital-sites (and associated countries) with the start- & stop-dates (for the study) and total (expected) patients to be recruited.
My approach thus far has been to calculate a Daily Recruitment-Rate per Site (’Total expected patients’/days between start and stop date) and use Power-query to feed an Expanded List of individual rows for: [Active calendar days] per [Site], [ Country], [[X_daily recruit](Site-specific Recruitment Rate) and an [Index-coumn] into my data-model.

For the +/- 100 sites, the data model now holds a +75k-row table, where each line specifies an individual day, pertaining to a site and country, with its site-specific “daily Recruitment Rate”. [RR_Expanded]

With a Calendar-table in the data modelit shows the plotted line (Forecast X) beautifully with the typical forecast-measure for a running total:

Forecast X:=CALCULATE (

    SUMX (RR_Expanded, RR_Expanded[X_daily recruit]),

    FILTER (

        ALL ( 'Calendar'[Date] ),

        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )

    )

)

(note that i'm (still) using excell, as our client is not yet onboard with Power-BI.)

However, I want to visualize and explore multiple scenarios where for instance a country experiences a 4-month delay in start-up. To do this, I can create a separate table with redefined start-dates for each scenario and create a new measure [say: Forcast Y] to show an additional forecast-line in my graph.

Unfortunately, this makes the excel file increasingly large and sluggish and... (on general principle) I’m hoping to learn a more dynamic approach using DAX.

As such, would it be possible to formulate logic based on the above forecast-formula (say Forecast Y) that uses the [RR_Expanded] listing but excludes all rows that have [Country] as e.g. “Russian” AND where the [Active calendar days] < “DateX” (effectively excluding the [X_daily recruit]’s from the cumulative forecast formula for Russia before a certain date).

So far, it seems impossible (for me) to apply a filter based on a combination of two column-criteria. All my efforts have resulted in the addition of two column-filters, filtering ‘RR_Expanded’ for ALL ‘Russian entries’ + ALL ‘date-entries <DateX’.

Any help is much appreciated! It has been learning by doing, but I really feel I hit a wall with this one.

Cheers!

[v-shex-msft]

HI @MEverts ,

You can test with following measure formula,t I ignore all filters on 'RR_Expanded' table and calculate rolling total based on selected calendar date(four months delay) and exclude country "Russian":

Forecast Y :=
VAR currDate =
    MAX ( 'Calendar'[Date] )
VAR delayDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 4, DAY ( currDate ) )
RETURN
    CALCULATE (
        SUM ( RR_Expanded[X_daily recruit] ),
        FILTER (
            ALL ( 'RR_Expanded' ),
            'RR_Expanded'[Date] <= delayDate
                && [Country] <> "Russian"
        )
    )

Regards.

Xiaoxin Sheng

[MEverts]

Thank you so much for your reply!
It seems however that, as with my initial efforts, the full source table for the rolling totals is filtered completely by each of the individual statements, and not limited to unique cases where both filter statements (Country=Russia AND start-date=<DateX) are TRUE.

Basically, I want to have the rolling SUM of [X_daily recruit], but exclude the sum of rows were [Country]=bb AND the [Date]=<Feb-20. (in purple).

[Site]	[Country]	[Date]	[X_daily recruit]
1	aa	20-Jan	0.5
2	aa	20-Feb	0.5
3	aa	20-Mar	0.5
4	aa	20-Apr	0.5
5	aa	20-May	0.5
6	bb	20-Jan	0.6
7	bb	20-Feb	0.6
8	bb	20-Mar	0.6
9	bb	20-Apr	0.6
10	bb	20-May	0.6
11	cc	20-Jan	0.3
12	cc	20-Feb	0.3
13	cc	20-Mar	0.3
14	cc	20-Apr	0.3
15	cc	20-May	0.3
16	dd	20-Feb	0.8
17	dd	20-Mar	0.8
18	dd	20-Apr	0.8
19	dd	20-May	0.8

Currently however, it seems:

            ALL ( 'RR_Expanded' ),
            'RR_Expanded'[Date] <= delayDate
                && [Country] <> "Russian"

is combining both filter statements.

Is there any way in DAX to exclude/filter for rows that distinctly meet criteria from two separate columns?

thanks!

[v-shex-msft · Accepted solution]

HI @MEverts ,

In fact, && means conditions are use 'and' logic to link, It will filter records who less than specific date and country not equal to particular value.
If you want to get rolling records, then exclude specific condition records, you can try to append these conditions after original rolling formula:

Forecast Y :=
VAR currDate =
    MAX ( 'Calendar'[Date] )
VAR delayDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 4, DAY ( currDate ) )
RETURN
    CALCULATE (
        SUM ( RR_Expanded[X_daily recruit] ),
        FILTER (
            ALL ( 'RR_Expanded' ),
            'RR_Expanded'[Date] <= delayDate
                && NOT (
                    AND ( 'RR_Expanded'[Date] < Feb - 20, [Country] = "bb" )
                )
        )
    )

Regards,

Xiaoxin Sheng

[MEverts]

Thank you so much Xiaoxin, this means a lot! the rolling totals are filtered as required.

Best regards,

Martijn