Forum Discussion

B_Rax's avatar
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 uni...
  • speedramps's avatar
    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, 😀