cancel
Showing results for
Did you mean:
Frequent Visitor

## Running Totals in descending order with filters

Hello,

I'm not sure why I am not getting this so I could please use some help.  I have the following dataset:

I need to be able to graph the following measures:

1. Planned Count = starting at min value in Planned Finish, summing all Planned Count and decreasing to zero, including every day from a dates table so graphing is smooth
1. example: starts at 8/28/20 with the first value of 13.  Stays at 13 until 9/11/20 where it drops to 11 (2 tasks completed on same day), then 10 on 1/29/21, etc. until it reachs zero on 8/27/21
2. Commit Count = Same as Planned count, just counting items with non-blank values Commit Finish
3. Actual Count = Same as Planned count, just counting items with non-blank values Actual Finish

Any assistance would be appreciated!

Thank you!!

2 REPLIES 2
Frequent Visitor

Unfortunately this produces lines that start with the first date in the date table instead of the first date where there is data and it also produces flat lines for actual.

The lines should be based on the original data:
Planned: starts at 8/28/2020 and steps down from 13 to 0
Actual: starts at 8/28/20 and starts at 13 and counts down 8
Commit: starts at 7/19/21 and starts at 5 and counts down to zero.

Actual + Commit = Planned

Start dates are always the first date where there is a count.

Existing graph:

Super User

@jschaefer , I think you need

Reverse cumulative

Join all three dates with date table

then create measures like

planned = sum(Table[Planned Count])
Commit = calculate(sum(Table[Commit Count]), userelationship('Date'[date], Table[Commit Finish]), not(isblank(Table[Commit Finish])))
Actual = calculate(sum(Table[Commit Count]), userelationship('Date'[date], Table[Actual Finish]), not(isblank(Table[Actual Finish])))

planned **bleep** = calculate([planned], filter(allselected('Date'),'Date'[date] >=max('Date'[date])))
Commit **bleep** = calculate([Commit], filter(allselected('Date'),'Date'[date] >=max('Date'[date])))
Actual **bleep** = calculate([Actual], filter(allselected('Date'),'Date'[date] >=max('Date'[date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

refer to my blog on similar topic if needed

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors