Forum Discussion

SzymonKl's avatar
SzymonKl
Icon for Helper I rankHelper I
2 years ago
Solved

undefinedHelp Needed: Summing Quantities Over a Date Range in Power BI

Hi Power BI Community,

I'm working on a report that requires summing quantities over a specified date range for each supplier. Here are the details:

Tables Involved:

  • SUPPLIERS: Contains information about suppliers.
  • SUPPLIER_TRANSFER_REQUESTS: Contains transfer data.
  • DEHIRE_LOGS: Contains dehire data.
  • DWELL_TIMES: Contains the allowance days.
  • CALENDAR: Contains calendar dates.
  • DAILY_BALANCE: Contains daily balance quantities.

Objective: For each supplier, I need to calculate the total quantity from the DEHIRE_LOGS table for a range of dates defined by the selected date in the slicer minus the allowance days from the DWELL_TIMES table.

Example: If the selected date in the slicer is 17/06/2024 and the allowance is 8 days, the date range should be from 09/06/2024 to 17/06/2024. The measure should sum the QUANTITY from the DEHIRE_LOGS table for this date range.

Steps Taken:

  1. Created a measure to calculate the start date:

    StartDateCheck = VAR SelectedDate = MAX(CALENDAR[Date]) VAR Allowance = MAX(DWELL_TIMES[ALLOWANCE]) RETURN SelectedDate - Allowance

  2. Created a measure to sum the quantities over the date range:

    TotalDehireQuantity = VAR SelectedDate = MAX(CALENDAR[Date]) VAR Allowance = MAX(DWELL_TIMES[ALLOWANCE]) VAR StartDate = SelectedDate - Allowance RETURN CALCULATE( SUM(DEHIRE_LOGS[QUANTITY]), FILTER( ALL(DEHIRE_LOGS), DEHIRE_LOGS[DEHIRED_AT] >= StartDate && DEHIRE_LOGS[DEHIRED_AT] <= SelectedDate ) )

Issue: Despite the steps above, the measure only shows the QUANTITY for the selected date, not the sum over the specified date range.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi SzymonKl 

     

    I made some changes to your code:

     

     

    Measure = 
var selectDate = SELECTEDVALUE('Calendar'[Date])
var Allowance = MAX('Dwell_Times'[Allowance])
var startDate = selectDate - Allowance
RETURN 
CALCULATE(
    SUM('Dehire_logs'[Quantity]),
    FILTER(
        ALLSELECTED('Dehire_logs'[Dehired_AT]),
        'Dehire_logs'[Dehired_AT] >= startDate
        &&
       'Dehire_logs'[Dehired_AT] <= selectDate
    )
)

     

     

     

     

          

     

     

    If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

     

    Regards,

    Nono Chen

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi SzymonKl 

     

    I made some changes to your code:

     

     

    Measure = 
var selectDate = SELECTEDVALUE('Calendar'[Date])
var Allowance = MAX('Dwell_Times'[Allowance])
var startDate = selectDate - Allowance
RETURN 
CALCULATE(
    SUM('Dehire_logs'[Quantity]),
    FILTER(
        ALLSELECTED('Dehire_logs'[Dehired_AT]),
        'Dehire_logs'[Dehired_AT] >= startDate
        &&
       'Dehire_logs'[Dehired_AT] <= selectDate
    )
)

     

     

     

     

          

     

     

    If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.

     

    Regards,

    Nono Chen

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