Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mooseuk
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
MFelix
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.