I currently have a table of rental equipment and a list of contracts they were assigned to with dates. My goal is to create a visual that will give me a breakdown of equipment type, the number of units, the number of days avaialbe across all units, and the number of days assigned to contracts across all units within any given date window. Here is my sample pbix. This is my sample semantic model:Days Available = VAR add_date = SELECTEDVALUE(Units[AddDate]) VAR min_date = MIN('Calendar'[Date]) VAR start_date = IF( add_date > min_date, add_date, min_date) VAR end_date = CALCULATE( MAX('Calendar'[Date]), 'Calendar'[Date] <= TODAY(), KEEPFILTERS('Calendar') ) VAR available_dates = DATESBETWEEN('Calendar'[Date], start_date, end_date) RETURN COUNTROWS(available_dates)Days Assigned = VAR add_date = SELECTEDVALUE(Units[AddDate]) VAR min_date = MIN('Calendar'[Date]) VAR start_date = IF( add_date > min_date, add_date, min_date) VAR end_date = CALCULATE( MAX('Calendar'[Date]), 'Calendar'[Date] <= TODAY(), KEEPFILTERS('Calendar') ) VAR available_dates = DATESBETWEEN('Calendar'[Date], start_date, end_date) VAR contract_dates = DATESBETWEEN('Calendar'[Date], SELECTEDVALUE('Lease Contracts'[StartDate]), SELECTEDVALUE('Lease Contracts'[EndDate])) VAR dates_joined = NATURALINNERJOIN(available_dates, contract_dates) RETURN COUNTROWS(dates_joined) I've managed to create two unaggregated visuals. One for days available and one for days assigned. However, the measures used to calculate those values require UnitID and ContractID respectively to be accurate. That means they do not work when aggregated by EquipmentType. This is the general idea of my target visual: I'm not sure if I need some separate measure that aggregates the first two or if I need to rework the entire approach. I've considered calculated columns, but as far as I know they would not change values based on a date filter/slicer.

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 OnedriveClick here How 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/yyyyy For example there were 366 days in 2020 (leap year) but Unit 4 was added on 9Jan.So it was available 366 - 9 = 357It 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/2020then the availability and days in contract changes accordingly This 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, 😀

Forum Discussion

B_Rax

Helper I

1 year ago

Solved

How to aggregate measures with date context?

I currently have a table of rental equipment and a list of contracts they were assigned to with dates. My goal is to create a visual that will give me a breakdown of equipment type, the number of units, the number of days avaialbe across all units, and the number of days assigned to contracts across all units within any given date window. Here is my sample pbix.

This is my sample semantic model:

Days Available = 
VAR add_date = 
    SELECTEDVALUE(Units[AddDate])
VAR min_date = 
    MIN('Calendar'[Date])
VAR start_date = 
    IF( add_date > min_date, add_date, min_date)
VAR end_date = 
    CALCULATE(
        MAX('Calendar'[Date]), 
        'Calendar'[Date] <= TODAY(), 
        KEEPFILTERS('Calendar')
    )
VAR available_dates = 
    DATESBETWEEN('Calendar'[Date], start_date, end_date)
RETURN
    COUNTROWS(available_dates)

Days Assigned = 
VAR add_date = 
    SELECTEDVALUE(Units[AddDate])
VAR min_date = 
    MIN('Calendar'[Date])
VAR start_date = 
    IF( add_date > min_date, add_date, min_date)
VAR end_date = 
    CALCULATE(
        MAX('Calendar'[Date]), 
        'Calendar'[Date] <= TODAY(), 
        KEEPFILTERS('Calendar')
    )
VAR available_dates = 
    DATESBETWEEN('Calendar'[Date], start_date, end_date)
VAR contract_dates = 
    DATESBETWEEN('Calendar'[Date], SELECTEDVALUE('Lease Contracts'[StartDate]), SELECTEDVALUE('Lease Contracts'[EndDate]))
VAR dates_joined = 
    NATURALINNERJOIN(available_dates, contract_dates)
RETURN
    COUNTROWS(dates_joined)

I've managed to create two unaggregated visuals. One for days available and one for days assigned. However, the measures used to calculate those values require UnitID and ContractID respectively to be accurate. That means they do not work when aggregated by EquipmentType.

This is the general idea of my target visual:

I'm not sure if I need some separate measure that aggregates the first two or if I need to rework the entire approach. I've considered calculated columns, but as far as I know they would not change values based on a date filter/slicer.

Need Help

speedramps
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 here

How 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/yyyyy

For 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 accordingly

This 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, 😀

15 Replies

speedramps
Super User
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 here

How 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/yyyyy

For 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 accordingly

This 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, 😀
- B_Rax
  Helper I
  1 year ago
  I've been busy on other things, but got some time to review this solution. I believe it will work for my report. Thank you for the assistance. The helper measures for SUMX was the step I had not considered.
speedramps
Super User
1 year ago
We want to help you but your description is too vague.

Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That is a bit crazy. 😀

Please just give a simple non technical functional description of what you want, then let us suggest the solution. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confusion.
(In your description you have devoted 75% of text to proudly explaining whay you have got work and only 25% to the actual problem, which clouds and confusses the issue).

Rename columns to user friendly names. Avoid jargon.
Also provide the example desired output, with a clear step-by-step description of calculations the process flow.
Remember not to share private data ... we don't want you to get into trouble. 😧
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Remember you are gertting free expert help, so please put lots of proper effort to asking questions and providing examples.

Vaugue descriptions can waste your time and our time.

Look forward to helping you when the above information is forthcoming 😀
- B_Rax
  Helper I
  1 year ago
  Respectfully, I did describe my goal and issues. I linked a simple pbix example of what I'm working with. The dax I linked does work. The next step is what I need help with. "Vaugue descriptions can waste your time and our time." This was far from vague and a time waste. Please be specific in what more information you would like if you are interested in helping. Thank you.
speedramps
Super User
1 year ago
Look forward to helping you when the above information is forthcoming.
Perhaps another member will help you with what you have provided.
I apprecaite it must be frustrating you cant do your can't do your job,

but rememeber we unpaid volunteers who just want to help.

👍
- B_Rax
  Helper I
  1 year ago
  From your first post you have been hostile to me. And now you are blatantly insulting me. I'm sorry if you're having a bad day. I appreciate your attempt to provide assitance, but would suggest if you tell others to be diligent and spend time crafting the perfect post that you also spend time actually reading and understanding that post. Also, I recommend proofreading your responses as they read very unprofessionally (even for an unpaid volunteer). Again, thank you.

speedramps

Super User

1 year ago

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 😀

B_Rax

Helper I

1 year ago

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.

speedramps
Super User
1 year ago
Thanks. Much appreciated !
I am trying to build some of the measure now,
but it it is getting late here. So might be tomorrow.
Good night👍
- B_Rax
  Helper I
  1 year ago
  Sleep well and sweet dreams.
speedramps
Super User
1 year ago
B_Rax
Download this PBIX example solution from Onedrive
Click here

So far I hace only created 1 measure. I will try do the other tomorrow.
Note I have used UK date formats

How it works ...

Add a detached calendar table with contigous dates for the entire date range.

Note the date window changes contract 5 from 5 to 3 days, and all te unit and type totals

Hope this helps ?

Anonymous

Not applicable

1 year ago

Hi B_Rax ,
You can try the following steps:

First create a date table

Date = 
VAR startDate = 
SWITCH(
    TRUE(),
    MIN('Lease Contracts'[StartDate]) <= MIN('Lease Contracts'[EndDate]) && MIN('Lease Contracts'[StartDate]) <= MIN(Units[AddDate]), MIN('Lease Contracts'[StartDate]),
    MIN('Lease Contracts'[EndDate]) <= MIN('Lease Contracts'[StartDate]) && MIN('Lease Contracts'[EndDate]) <= MIN(Units[AddDate]), MIN('Lease Contracts'[EndDate]),
    MIN(Units[AddDate])
)
VAR endDate = 
SWITCH(
    TRUE(),
    MAX('Lease Contracts'[StartDate]) >= MAX('Lease Contracts'[EndDate]) && MAX('Lease Contracts'[StartDate]) >= MAX(Units[AddDate]), MAX('Lease Contracts'[StartDate]),
    MAX('Lease Contracts'[EndDate]) >= MAX('Lease Contracts'[StartDate]) && MAX('Lease Contracts'[EndDate]) >= MAX(Units[AddDate]), MAX('Lease Contracts'[EndDate]),
    MAX(Units[AddDate])
)
RETURN
CALENDAR(startDate,endDate)

Second, Create mesures

Unit Count = 
CALCULATE(
    DISTINCTCOUNT('Lease Contracts'[UnitID]),
    FILTER(
        'Lease Contracts',
        'Lease Contracts'[StartDate] >= MIN('Date'[Date]) &&
        'Lease Contracts'[EndDate] <= MAX('Date'[Date])
    )
)

AvailableDays = 
DATEDIFF(
    MIN('Lease Contracts'[StartDate]),
    MAX('Lease Contracts'[EndDate]),DAY)
+ 2

Days Assigned = 
VAR addDate = 
CALCULATE(
    COUNTROWS('Lease Contracts'),
    FILTER(
        ALLEXCEPT(
            'Lease Contracts',
            Units[EquipmentType]
        ),
        'Lease Contracts'[StartDate] >= MIN('Date'[Date]) &&
        'Lease Contracts'[EndDate] <= MAX('Date'[Date])
    )
)
RETURN
CALCULATE(
    SUMX(
        'Lease Contracts',
        DATEDIFF('Lease Contracts'[StartDate],'Lease Contracts'[EndDate],DAY)
    ),
    FILTER(
        'Lease Contracts',
        'Lease Contracts'[StartDate] >= MIN('Date'[Date]) &&
        'Lease Contracts'[EndDate] <= MAX('Date'[Date])
    )
) + addDate

Utilization = DIVIDE([Days Assigned],[AvailableDays],0)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

How to aggregate measures with date context.pbix75 KB

speedramps
Super User
1 year ago
B_Rax
Have you any feedback on the solution yet?

I am avaialable for a little longer today and then really busy Friday, ans then we go into the weekend.

So hope we can concluded any Q&As today, to get you a quick answer.
speedramps
Super User
1 year ago
Hi agaib B_Rax

We haven't received any feedback from you regarding the solution.

But I recall how quickly you responded vigorously when promoted for example data 🤔
If we dont here from you, then the solution will be accepted after a period,

but we would like to hear how you got on.

Thank you for using the community forum ! 👍
speedramps
Super User
1 year ago
Thanks B_Rax

Hope to help you again in future.

It really helps if you attach data as text rather than screen print. Cheers.

Forum Discussion

How to aggregate measures with date context?

15 Replies

Related content

Calculate Sumx aggregation - filter context - issue

Slicers on Aggregated Measures

Aggregation in context

Measure excluding row context

Dax measure not considering the filter context and not aggregating