Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for your reply, here is some data: 

     

    Refill

    date_of_refillBox
    01.04.2024X
    06.04.2024Y
    09.04.2024Y
    11.04.2024Y
    15.04.2024Y
    17.04.2024X

     

    Deliveries

    Delivery_dateQuantityBox
    01.04.202425X
    02.04.202425Y
    03.04.202423X
    04.04.202456X
    05.04.202423Y
    06.04.202443Y
    07.04.202412X
    07.04.202445Y
    08.04.202434X
    08.04.202432Y
    08.04.202442Y
    09.04.202425X
    09.04.202425Y
    10.04.202423Y
    11.04.202456X
    12.04.202423X

     

    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.202443Y
    07.04.202445Y
    08.04.202432Y

     

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

     

    Make sense? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

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