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 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.

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.

     

     

10 Replies

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

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

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

      • 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.