Summarizing values and dates in one table to fall between dates in another.

[PauSe]

Business Process: A warehouse is divided into storage areas called "boxes". The boxes are huge and receive many deliveries each day and material is also taken out.

A table called Deliveries records the date, quantity of each delivery (100's per month) and the box in which the delivery is stored.

Occasionally, the box is completely emptied, cleaned and then re-opened.

A table called BoxRefill records a Refill date, which is the date when a box is emptied, cleaned and re-opened for deliveries.

The requirement is to show the average quantity in a box, when a User selects a Refill date from E.g., a Slicer.

The visual must therefore, group the Deliveries and match them to how they fall between Refill dates.

Just to repeat the challenge, if the Report User clicks on Refill 09.04.2024, then the report should show the average quantity of all deliveries between 06.04.2024 (the previous date when the box was emptied and re-opened for new deliveries) and 09.04.2024, when the box is once more emptied.

Refill,  Box
01.04.2024 X
06.04.2024 X
09.04.2024 Y
11.04.2024 Y
15.04.2024 Y
17.04.2024 X
23.04.2024 Y
25.04.2024 X
28.04.2024 X
30.04.2024 X

I am playing with the ideas of adding indexes to BoxRefill, also considering adding start and end date columns to each Refill date. But I cannot figure out how to match the delivery dates to the BoxRefill table.  I thought DATESBETWEEN seemed a good option but my brain cannot figure out how to use it for this problem!

[PauSe]

Thanks for your reply, here is some data: 

Refill

date_of_refill	Box
01.04.2024	X
06.04.2024	Y
09.04.2024	Y
11.04.2024	Y
15.04.2024	Y
17.04.2024	X

Deliveries

Delivery_date	Quantity	Box
01.04.2024	25	X
02.04.2024	25	Y
03.04.2024	23	X
04.04.2024	56	X
05.04.2024	23	Y
06.04.2024	43	Y
07.04.2024	12	X
07.04.2024	45	Y
08.04.2024	34	X
08.04.2024	32	Y
08.04.2024	42	Y
09.04.2024	25	X
09.04.2024	25	Y
10.04.2024	23	Y
11.04.2024	56	X
12.04.2024	23	X

I will have a two Slicers for Box and Date_of_Refill, both sourcing from the Refill table.

If I click the Slicer on Box value "Y" and Date_of_Refill "09.04.2024" then I want to see the Average Quantity of deliveries before that data and the previous Refill date (06.04.2024). The calculation should include these rows from the Deliveries table: 

06.04.2024	43	Y
07.04.2024	45	Y
08.04.2024	32	Y

So, for example a Card visual would show an average quantity of 40.

Make sense? 

[v-yiruan-msft · Accepted solution]

Hi @PauSe ,

You can follow the steps below to get it, please find the details in the attachment.

1. Create the relationship between the table 'Deliveries' and 'Refill' base on the field [Box]

2. Create a measure as below to get the average quantity

Average quantity = 
VAR _date =
    SELECTEDVALUE ( 'Refill'[date_of_refill] )
VAR _predate =
    CALCULATE (
        MAX ( 'Refill'[date_of_refill] ),
        FILTER ( ALL ( 'Refill' ), 'Refill'[date_of_refill] < _date )
    )
RETURN
    CALCULATE (
        AVERAGE ( 'Deliveries'[Quantity] ),
        FILTER (
            ALLSELECTED ( 'Deliveries' ),
            'Deliveries'[Delivery_date] >= _predate
                && 'Deliveries'[Delivery_date] < _date
        )
    )

Best Regards

[PauSe]

Thankyou so much. I have applied this to a more complex data set and it works! 

[v-yiruan-msft]

Hi @PauSe ,

Base on your description, it seems that you want to get the average quantity between current refill data and previous refill date. Which table is the field [quantity] from? Could you please provide some raw data in your table 'Deliveries' and 'BoxRefill' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

In addition, you can refer the following links to get the values for the previous date.

Solved: calculate Difference from previous date - Microsoft Fabric Community

Getting Previous Values in Power BI - Part 1 - Microsoft Fabric Community

Best Regards