Create a table of values based on two dates

[DataUsurper]

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]

Hi @DataUsurper ,

Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[DataUsurper]

File link: https://1drv.ms/x/s!AslZBEiuR4xY83ifiEQluCNKjUs_?e=8nvWNm

[v-frfei-msft · Accepted solution]

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.

[DataUsurper]

Evening/morning @v-frfei-msft, got a question for ya on implementing this within a rather large table. When I attempted to create the initial step 1 table on my report file, it appears stuck/seriously-working-hard on "Working on it" for some 30 minutes now. My report file has a 417 week range from 285K unique orderIDs with a varying number of 118K product codes; my LineItemDetails table has 8.6M records.

I used a dates and days table by orderID to generate the minx/maxx calendar range, then that 8.6M records table to determine orderid/ecode records.

Suggestions?

[DataUsurper]

I thought about consolidating steps 1 - 3 to try and bypass loading a lot of data, but same result..."Working on it"

wtbl_WROR = 
VAR a =
    CALENDAR(MINX(tbl_Orders_DatesDays,tbl_Orders_DatesDays[SHIP_DATErpt]),MAXX(tbl_Orders_DatesDays,tbl_Orders_DatesDays[RETURN_DATErpt]))
VAR b =
    SELECTCOLUMNS(
        tbl_Orders_LineItemDetails,
        "sORDERID",tbl_Orders_LineItemDetails[SUB_ORDER],
        "eCODE",tbl_Orders_LineItemDetails[eCODE]
    )
VAR ship =
    CALCULATE(
        MAX(tbl_Orders_DatesDays[SHIP_DATErpt]),
        FILTER(tbl_Orders_DatesDays,tbl_Orders_DatesDays[SUB_ORDER]="sORDERID")
    )-7
VAR returndate =
    CALCULATE(
        MAX(tbl_Orders_DatesDays[RETURN_DATErpt]),
        FILTER(tbl_Orders_DatesDays,tbl_Orders_DatesDays[SUB_ORDER]="sORDERID")
    )
RETURN
    FILTER(
        CROSSJOIN(a,b),WEEKDAY([Date],2)=1 && (IF([Date]>=ship && [Date]<= returndate,1,0))=1)

[DataUsurper]

Thank you Frank, that does it after one minor adjustment to the final revenue step to also link orders to orders:

wkly_Revenue = 
VAR suma =
    CALCULATE(SUM(tbl_WROR[Days_In_Use]),ALLEXCEPT(tbl_WROR,tbl_WROR[eCODE],tbl_WROR[sORDERID]))
VAR val =
    CALCULATE(
        SUM(tbl_TstData[ITEMTOTAL]),
        FILTER(tbl_TstData,tbl_TstData[eCODE]=tbl_WROR[eCODE] &&
                tbl_TstData[SUB_ORDER]=tbl_WROR[sORDERID])
    )
RETURN
    DIVIDE(val,suma)*tbl_WROR[Days_In_Use]

 This looks like it might had been quite the challenge to determine, so thank you again good sir.

[DataUsurper]

Thanks @v-frfei-msft, am testing this morning.

[DataUsurper]

Sure thing, will create a larger sample file and send you the link.

[DataUsurper]

Friendly bump

[DataUsurper]

Am also open to a more efficient method.