Forum Discussion

DataUsurper's avatar
DataUsurper
Helper II
6 years ago
Solved

Create a table of values based on two dates

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 ne...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

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's avatar
DataUsurper
Helper II
6 years ago

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's avatar
    DataUsurper
    Helper II
    6 years ago

    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)