cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## calculate running total of consecutive value

Hi,

I am trying to calcualte running total of only consecutive 1's value.

condition:

1. Date range is selected in date slicer. So date range is always dynamic.

2. Running total should be done in ascending order of date and should be done fir individual resources.

3. whenever 0 is encountered running total should again restart for eg. for resource "abc" for 1/5/2020 Billable value is 0. so for 1/6/2020 running total is again calculated from beginning.

 Date Resource Billable running total 1/2/2020 abc 1 1 1/3/2020 abc 1 2 1/4/2020 abc 1 3 1/5/2020 abc 0 0 1/6/2020 abc 1 1 1/7/2020 abc 1 2 1/2/2020 xyz 0 0 1/3/2020 xyz 1 1 1/4/2020 xyz 0 0 1/5/2020 xyz 1 1 1/6/2020 xyz 1 2 1/7/2020 xyz 0 0

Regards

Vaishali Rathi

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User V

## Re: calculate running total of consecutive value

Try this, I'm hoping someone will come up with a more efficient version, but for now, at least you have some solution.

``````Column =
VAR __date = 'Table'[Date]
VAR __allResource = ALLEXCEPT( 'Table', 'Table'[Resource] )
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)``````

See the attached for ref as well

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Highlighted
Super User V

## Re: calculate running total of consecutive value

Ok, I've added ALLSELECTED

``````Measure =
VAR __date = SELECTEDVALUE( 'Table'[Date] )
VAR __allResource =
CALCULATETABLE(
'Table',
ALLSELECTED( 'Table' ),
VALUES( 'Table'[Resource] )
)
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

5 REPLIES 5
Highlighted
Super User V

## Re: calculate running total of consecutive value

Try this, I'm hoping someone will come up with a more efficient version, but for now, at least you have some solution.

``````Column =
VAR __date = 'Table'[Date]
VAR __allResource = ALLEXCEPT( 'Table', 'Table'[Resource] )
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)``````

See the attached for ref as well

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Highlighted
Frequent Visitor

## Re: calculate running total of consecutive value

Thanku @Mariusz for quick reply. Logic is really awsm.

But I  want the logic to work on dynamic date range. Can you please convert  your logic in a measure instead of a column. I am a  beginner in power bi so finding it hard to convert  as of now.

Thanks a ton 🙂

Regards

Vaishali

Highlighted
Super User V

## Re: calculate running total of consecutive value

No problem, please see the below

``````Measure =
VAR __date = SELECTEDVALUE( 'Table'[Date] )
VAR __allResource = ALLEXCEPT( 'Table', 'Table'[Resource] )
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Highlighted
Frequent Visitor

## Re: calculate running total of consecutive value

Thanku @Mariusz

But I have a  concern. In measure _firstzero and _start variable are not getting adjusted with slider date selections at UI.

Regards

Vaishali Rathi

Highlighted
Super User V

## Re: calculate running total of consecutive value

Ok, I've added ALLSELECTED

``````Measure =
VAR __date = SELECTEDVALUE( 'Table'[Date] )
VAR __allResource =
CALCULATETABLE(
'Table',
ALLSELECTED( 'Table' ),
VALUES( 'Table'[Resource] )
)
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Announcements

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors