Forum Discussion
Solved
Cap Sum by Day
I am not new to DAX but I’m not an expert either. I need some help, please, developing a calculation and I’m not sure the best approach. My data model captures data about utilization of resources. ...
you sample data and the expected outcome don't agree.
Anyway, here's my proposal. You could hide the extra "cap" columns or ignore them.
Here is some sample data:
| Date | Month-Year | Hour | Resource | Duration Used |
| 20230101 | January 2023 | 16 | 1 | 60 |
| 20230101 | January 2023 | 16 | 2 | 60 |
| 20230101 | January 2023 | 16 | 3 | 60 |
| 20230101 | January 2023 | 16 | 4 | 60 |
| 20230101 | January 2023 | 16 | 5 | 60 |
| 20230101 | January 2023 | 17 | 1 | 59 |
| 20230101 | January 2023 | 17 | 2 | 60 |
| 20230101 | January 2023 | 17 | 3 | 60 |
| 20230101 | January 2023 | 17 | 4 | 60 |
| 20230101 | January 2023 | 17 | 5 | 1 |
| 20230101 | January 2023 | 18 | 1 | 60 |
| 20230101 | January 2023 | 18 | 2 | 60 |
| 20230101 | January 2023 | 18 | 3 | 60 |
| 20230101 | January 2023 | 18 | 4 | 60 |
| 20230101 | January 2023 | 18 | 5 | 60 |
| 20230102 | January 2023 | 16 | 1 | 60 |
| 20230102 | January 2023 | 16 | 2 | 60 |
| 20230102 | January 2023 | 16 | 3 | 60 |
| 20230102 | January 2023 | 16 | 4 | 60 |
| 20230102 | January 2023 | 16 | 5 | 60 |
| 20230102 | January 2023 | 17 | 1 | 60 |
| 20230102 | January 2023 | 17 | 2 | 60 |
| 20230102 | January 2023 | 17 | 3 | 60 |
| 20230102 | January 2023 | 17 | 4 | 60 |
| 20230102 | January 2023 | 17 | 5 | 1 |
| 20230102 | January 2023 | 18 | 2 | 60 |
| 20230102 | January 2023 | 18 | 3 | 60 |
| 20230102 | January 2023 | 18 | 4 | 60 |
| 20230102 | January 2023 | 18 | 5 | 60 |
| 20230102 | January 2023 | 16 | 1 | 60 |
Here are the expected results:
- lbendlin
Super User
you sample data and the expected outcome don't agree.
Anyway, here's my proposal. You could hide the extra "cap" columns or ignore them.
Beautiful, thank you! I use an uncommon combo of tools (Tableau, Tabular Editor, and SSAS) - no Power BI...yet. I'll plug this into TE3 and give it a shot, but your results are spot on. Sorry about the sample data being off. Nothing like bad data to throw off the example, but glad you caught it.