Forum Discussion
How to aggregate measures with date context?
- 1 year ago
Hi again B_Rax
Thank you Anonymous for you alternative solution.
I am not sure it produces the answers B_Rax wants.It very complicated for novices like B_Rax to learn.
It contains lots of branches in SWITCH statements, which will each need rigorously testing.
And it is difficult to change if the user decides they want to exclude weekends and holidays.
I therefore respectfully suggest this PBIX solution which you can download from Onedrive
Click hereHow it works ...
Use a detached Calendar table with contiguous dates for your slicer date window
Create a "slave" measure to calculate days avaialble
_Days available = // this measure must be used a XSUMX wrapper // get the unit add date var unitstart = SELECTEDVALUE(Units[AddDate]) // create a temp file of dates using the date slicer window and add date var mydays = FILTER('Calendar', 'Calendar'[Date] > unitstart ) RETURN // count the number of dates COUNTROWS(mydays)Then a "master" measure to iterarte the slave for each unit in your reporting context (eg contracts, units or equipment type)
Days available = // this measure uses the SUMX function to iterate the calcuation for any unit var daysavailabe = SUMX( Units, [_Days available] ) RETURN // avaiability can only be measured at unit or type level but not contract level IF(ISINSCOPE(Contracts[ContractID]), BLANK(), daysavailabe)Create a "slave" measure to calculate days on contract
_Days on contract = // this measure must be used a XSUMX wrapper // get the contract date range var contractstart = SELECTEDVALUE(Contracts[StartDate]) var contractend = SELECTEDVALUE(Contracts[EndDate]) // create a temp file of dates using the date slicer window and contract date var mydays = FILTER('Calendar', 'Calendar'[Date] >= contractstart && 'Calendar'[Date] <= contractend ) RETURN // count the number of dates COUNTROWS(mydays)Then a "master" measure to iterarte the slave for each unit in your reporting context.
Days on contract = // this measure uses the SUMX function to iterate the calcuation for any contract SUMX( Contracts, [_Days on contract] )When you run the the report at equipment type level then the totals roll up automatically
Page 1 shows the inner workings with the "slaves".
Page 2 shows the final reports with just the "masters" and without the "slaves"
Note all dates are in UK dd/mm/yyyy format but you can display them in USA mm/dd/yyyyyFor example there were 366 days in 2020 (leap year) but Unit 4 was added on 9Jan.
So it was available 366 - 9 = 357
It had a contract for 6 days.
Usage was 6 / 357 = 1.68%If you change the date slicer window to 12/01/2020 to 31/12/2020
then the availability and days in contract changes accordinglyThis method is easy to understand and learn, and easy to test because it does not have lots of switch branches.
If you just want to include working days and exclude weekends and holidays then you could add a working days column to the Calendar table, and aggregate that rather than count rows.Please click thumbs up for this suggestion (because I did spend a lot of time on it)
and also click [accept solution] if it works.
Hope it helps and makes up for our rocky start. 👍Warm regards, 😀
I had a great day thank you and solved other members issues.
If you provide the information requested I will be happy to help you.
Otherwise I hope another Super User helps you.
Perhaps ask a freind, collegaue or manager to see if they can help you reword your description.
Remember, we do want to help now and in the future 😀
Again, the information you requested is in the original post and provided example pbix. However, I'll attempt to highlight the main points and provide tables here if you are unable to view the example file. The measures involved are in the original post.
I have two tables (plus a date table):
- Lease Contracts - table of contracts including the relevant unit and the start/end dates of the contract period.
- Units - table of units in inventory, their equpment type, and the date in which they were added.
Goal:
Create a visual that shows for any date context:
- Each equipment type
- Number of days that type has been available to be on contracts across all units
- Number of days that type has been on contract across all units
- Ratio between available and assigned (utilization).
Process:
I have three measures:
- Days Availabe - Calculates the number of days a unit was avaiable to be on contracts.
- Days Assigned - Calculates the number of days a unit was actually on contracts.
- Utilization - Divdes days assigned by days available.
These measures work, but not for the target visual that aggregates by equipment type. Therefore, I either need a different approach to these calculations or to add additional steps.
Lease Contracts
| ContractID | UnitID | StartDate | EndDate |
| 1 | 1 | 01/01/2020 | 01/03/2020 |
| 2 | 1 | 01/04/2020 | 01/04/2020 |
| 3 | 2 | 01/01/2020 | 01/02/2020 |
| 4 | 3 | 01/10/2020 | 01/15/2020 |
| 5 | 3 | 01/16/2020 | 01/20/2020 |
| 6 | 3 | 02/01/2020 | 02/05/2020 |
| 7 | 5 | 02/02/2020 | 02/04/2020 |
| 8 | 4 | 01/10/2020 | 01/15/2020 |
| 9 | 1 | 01/06/2020 | 01/08/2020 |
| 10 | 1 | 01/09/2020 | 01/12/2020 |
Units
| UnitID | AddDate | EquipmentType |
| 1 | 01/01/2020 | Truck |
| 2 | 01/01/2020 | Truck |
| 3 | 01/05/2020 | Trailer |
| 4 | 01/09/2020 | Trailer |
| 5 | 02/02/2020 | Truck |
Example Desired Result for 1/1/2020 - 1/20/2020
| EquipmentType | Unit Count | Days Available | Days Assigned | Utilization |
| Trailer | 2 | 28 | 17 | 60.7% |
| Truck | 2 | 40 | 13 | 32.5% |
If this continues to be unclear, please be specific in what you need. Thank you.