Solved: Create a table of values based on two dates - Page 2

DataUsurper · ‎10-12-2019

Hello All o/

I am working on a weekly rate of return review by product and order based on the orders' ship and return dates. I believe PQ is the best option to build this table, but I am still new to this environment and could definitely use some help. My raw data looks like:

I am trying to build a table that will break out the itemtotal per week's days-in-use between the ship and return dates with an orderid and product code field. Example:

I also have attached an example file, but please note that the first and last week's are dependent on the ship and return date rather than the week beginning and end dates; calculations are highlighted in the grey/orange. Let me know if any additional details are needed.
Thank you kindly
DU ; )

Edit: I don't see the add file buttahn on this area, PM me if you need the example file, it's only 17kb's.

v-frfei-msft · ‎10-14-2019

Hi @DataUsurper ,

Please check the following steps as below.

1. Create a calculated table as below.

Table = 
VAR a =
    CALENDARAUTO ()
VAR b =
    SELECTCOLUMNS (
        'Order',
        "id", 'Order'[MAIN_ORDER],
        "product", 'Order'[MAINCODE]
    )
RETURN
    FILTER ( CROSSJOIN ( a, b ), WEEKDAY ( [Date], 2 ) = 1 )

2. Based on that, we can a calculated column in that table.

Filter =
VAR ship =
    CALCULATE (
        MAX ( 'date days'[SHIP_DATE] ),
        FILTER ( 'date days', 'date days'[ORDERID] = 'Table'[id] )
    ) - 7
VAR returndate =
    CALCULATE (
        MAX ( 'date days'[RETURN_DATE] ),
        FILTER ( 'date days', 'date days'[ORDERID] = 'Table'[id] )
    )
RETURN
    IF ( 'Table'[Date] >= ship && [Date] <= returndate, 1, 0 )

3. Then we can get a filtered table.

new = FILTER('Table','Table'[Filter]=1)

4. In that table, we can achieve our goal by creating two more calculated columns.

Days in use = 
VAR st =
    CALCULATE (
        MAX ( 'date days'[SHIP_DATE] ),
        FILTER ( 'date days', 'date days'[ORDERID] = new[id] )
    )
VAR returndate =
    CALCULATE (
        MAX ( 'date days'[RETURN_DATE] ),
        FILTER ( 'date days', 'date days'[ORDERID] = new[id] )
    )
VAR maxdate =
    CALCULATE (
        MAX ( new[Date] ),
        FILTER (
            new,
            new[id] = EARLIER ( new[id] )
                && new[product] = EARLIER ( new[product] )
        )
    )
VAR mindate =
    CALCULATE (
        MIN ( new[Date] ),
        FILTER (
            new,
            new[id] = EARLIER ( new[id] )
                && new[product] = EARLIER ( new[product] )
        )
    )
RETURN
    IF (
        [Date] = mindate,
        DATEDIFF ( [Date], st, DAY ),
        IF ( [Date] = maxdate, DATEDIFF ( [Date], returndate, DAY ), 7 )
    )

Revenue = 
VAR suma =
    CALCULATE ( SUM ( new[Days in use] ), ALLEXCEPT ( new, new[product], new[id] ) )
VAR val =
    CALCULATE (
        SUM ( 'Order'[  ITEMTOTAL  ] ),
        FILTER ( 'Order', 'Order'[MAINCODE] = new[product] )
    )
RETURN
    DIVIDE ( val, suma ) * new[Days in use]

For more details, please check the pbix as attached.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Create a table of values based on two dates

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024