cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Running Total - Count Groups with Missing Values

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?

1 ACCEPTED SOLUTION
Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

2 REPLIES 2
Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

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