Running Total - Count Groups with Missing Values

[mooseuk]

I've been trying to figure this out for a while and don't think i've found a solution yet.  I need to provide a summary of a running total that is defined by the date in the row. 

For example, for person 1 below I need to identify that from the most recent episode (1/7/18), how many episodes have occurred in the prior 30 days (2/6/18)- in this case 2.   

For person 2, the result would also be 2 as the most recent episode is 15/7/18 and the first episode is within 30 days.

For person 3, the result is 1 as there are no other episodes in the date range.

Person ID / Episode Date

1 / 2018-05-01

1 / 2018-06-02

1 / 2018-07-01

2 / 2018-07-01

2 / 2018-07-15

3 / 2018-07-01

The result i'd be looking for would be

Total episodes within 30 days / Count meeting criteria

1 = 1

2 = 2

3 = 0

I'm also then looking to figure out a way to calculate this month on month so the table above might become:

Total episodes within 30 days / May / June / July - which i'm not sure is possible?

[MFelix · Accepted solution]

Hi @mooseuk,

Try the following measure:

Last 30 days =
VAR date_select =
    CALCULATE ( MAX ( Episodes[Episode Date] ); VALUES ( Episodes[Person ID] ) )
VAR Validation =
    CALCULATE (
        COUNT ( Episodes[Episode Date] );
        Episodes[Episode Date]
            >= date_select - 30
            && Episodes[Episode Date] <= date_select
    )
RETURN
    IF (
        CALCULATE ( COUNT ( Episodes[Episode Date] ); ALL ( Episodes[Episode Date] ) )
            = 1;
        0;
        Validation
    )

Regards,

MFelix

[mooseuk]

@MFelix Thanks that works a treat - think i'll be spending the weekend understanding that