Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jschaefer
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:

Task NamePlanned
Count
Planned
Finish
Commit
Count
Commit
Finish
Actual
Count
Actual
Finish
task 111/29/20210 11/29/2021
task218/28/20200 18/28/2020
task312/16/20210 12/16/2021
task419/11/20200 19/11/2020
task518/27/2021110/1/20210 
task614/30/20210 14/30/2021
task711/29/20210 11/29/2021
task817/30/202118/20/20210 
task916/7/20210 16/7/2021
task1017/19/202117/19/20210 
task1117/16/202118/6/20210 
task1217/23/202118/5/20210 
task1319/11/20200 19/11/2020

 

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
jschaefer
Frequent Visitor

@amitchandak While I admire your correct syntax your spelling of count might have missed something. ;-O

 

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.

 

After thinking about this a little more I realize my requirements change a little.  

 

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:

jschaefer_0-1626849538962.png

 

 

 

amitchandak
Super User
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...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors