cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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. 

 

 

DateResourceBillablerunning total
1/2/2020abc11
1/3/2020abc12
1/4/2020abc13
1/5/2020abc00
1/6/2020abc11
1/7/2020abc12
1/2/2020xyz00
1/3/2020xyz11
1/4/2020xyz00
1/5/2020xyz11
1/6/2020xyz12
1/7/2020xyz00

 

Please help if anyone knows the logic.

Regards

Vaishali Rathi

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User V
Super User V

Re: calculate running total of consecutive value

Hi @Vaishali04 

 

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.
LinkedIn

 

View solution in original post

Highlighted
Super User V
Super User V

Re: calculate running total of consecutive value

Hi @Vaishali04 

 

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.
LinkedIn

 

View solution in original post

5 REPLIES 5
Highlighted
Super User V
Super User V

Re: calculate running total of consecutive value

Hi @Vaishali04 

 

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.
LinkedIn

 

View solution in original post

Helper I
Helper I

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
Super User V

Re: calculate running total of consecutive value

Hi @Vaishali04 

 

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.
LinkedIn

 

Highlighted
Helper I
Helper I

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
Super User V

Re: calculate running total of consecutive value

Hi @Vaishali04 

 

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.
LinkedIn

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors