Forum Discussion
Calculating a Total Based on Start/End Dates
Greg_Deckler
July 2019: N=7, Total ObjectXAttribute=5.25
7 Objects: A, B, C, D, E, F, G
Sum of ObjectXAttribute = 0.90 + 1.00 + 0.50 + 1.00 + 0.75 + 0.85 + 0.25 = 5.25
| Line# | Start Date | End Date | Site | Object | ObjectAttributeX |
| 1 | 7/1/2019 | 5 | A | 0.90 | |
| 2 | 7/1/2019 | 5 | B | 1.00 | |
| 3 | 7/1/2019 | 2/29/2020 | 5 | C | 0.50 |
| 4 | 3/1/2020 | 5 | C | 0.20 | |
| 5 | 7/1/2019 | 3/31/2020 | 5 | D | 1.00 |
| 6 | 4/1/2020 | 5 | D | 0.80 | |
| 7 | 7/1/2019 | 5 | E | 0.75 | |
| 8 | 7/1/2019 | 12/31/2019 | 5 | F | 0.85 |
| 9 | 7/1/2019 | 5 | G | 0.25 |
March 2020: N=6,Total ObjectXAttribute=4.10
6 Objects: A, B, C, D, E, G
Sum of ObjectXAttribute = 0.90 + 1.00 + 0.20 + 1.00 + 0.75 + 0.25 = 4.10
| Line# | Start Date | End Datw | Site | Object | ObjectAttributeX |
| 1 | 7/1/2019 | 5 | A | 0.90 | |
| 2 | 7/1/2019 | 5 | B | 1.00 | |
| 3 | 7/1/2019 | 2/29/2020 | 5 | C | 0.50 |
| 4 | 3/1/2020 | 5 | C | 0.20 | |
| 5 | 7/1/2019 | 3/31/2020 | 5 | D | 1.00 |
| 6 | 4/1/2020 | 5 | D | 0.80 | |
| 7 | 7/1/2019 | 5 | E | 0.75 | |
| 8 | 7/1/2019 | 12/31/2019 | 5 | F | 0.85 |
| 9 | 7/1/2019 | 5 | G | 0.25 |
April 2020: N=6, Total ObjectXAttribute=3.90
6 Objects: A, B, C, D, E, G
Sum of ObjectXAttribute = 0.90 + 1.00 + 0.20 + 0.80 + 0.75 + 0.25 = 3.90
| Line# | Start Date | End Date | Site | Object | ObjectAttributeX |
| 1 | 7/1/2019 | 5 | A | 0.90 | |
| 2 | 7/1/2019 | 5 | B | 1.00 | |
| 3 | 7/1/2019 | 2/29/2020 | 5 | C | 0.50 |
| 4 | 3/1/2020 | 5 | C | 0.20 | |
| 5 | 7/1/2019 | 3/31/2020 | 5 | D | 1.00 |
| 6 | 4/1/2020 | 5 | D | 0.80 | |
| 7 | 7/1/2019 | 5 | E | 0.75 | |
| 8 | 7/1/2019 | 12/31/2019 | 5 | F | 0.85 |
| 9 | 7/1/2019 | 5 | G | 0.25 |
Community ChampionOK, spewing numbers at me <> helping.
What is the logic going on here? Why are those numbers included with March 2020 versus April 2020, don't make me try to figure it out, just tell me. The only one that makes sense is July 2019, there are actually 7 rows wtih July 1 2019 as a Start Date. March, I have no idea why there are six March numbers to sum up. There is only one row that matches March 2020. Same for April. So why the magical 6 numbers?
Once an object starts, the ObjectAttributeX counts for every month thereafter until the end date.
For March, all the lines that started 7/1/2019 count towards the March total, except for lines 3 and 8 because they have end dates prior to 3/1/20.- Greg_Deckler
OK, should be something along the lines of:
Column = VAR __Table = FILTER(ALL('Table'),[Start Date]<=[Date] && ('Table'[End Date]>=[Date] || ISBLANK('Table'[End Date]))) RETURN SUMX(__Table,[ObjectAttributeX])Attached PBIX file
Makes sense. I understand how my statement saying Power BI does not support duration is misleading. It certainly does it just has do be in decimal format and then converted to duration in the article you posted. This is essentially what the post I included states as well though I understand saying duration is not supported is technically incorrect. Thanks for the learning moment 🙂
Anonymous
I think your answer may be found in this thread: https://community.powerbi.com/t5/Desktop/Count-of-Open-or-Closed-items-based-on-2-different-Date-Columns/td-p/739189
- Greg_Deckler
Thank-you. I will take a closer look. But, dude, how did you expect me to figure that out?
Greg_Deckler I'm sorry! very new to this, not sure how to explain accurately.
So I want the date that the data is filtered to be based off a datedimension table that i built. I put the DateDimension into a Slicer to filter the data in the "Report" tab.
I have 4 tables right now - a site key, the objectattributex by date, objectattributex need, and a date dimension:
Is it possible to make this work?