Plotting Calculated Weekly Revenue and/or Return % between BillStart and BillEnd Dates.

[DataUsurper]

Hello All  o/

I am attempting to create a measure to graph a calculated weekly revenue amount between an order's bill from and bill to dates. My data-model's context for those orders are per the orders' ship date, so I haven't linked either of the bill dates to the calendar table.

• The calculated Wkly Revenue is : (total/order days)*7
• The Weekly Rate of Return WROR is: Wkly Revenue/ReplacementCost

Is there an efficient way to produce this measure for graphing without creating a table (we tried that and it ended up producing 66 million records killing file performance. This was from the nifty work from a msft user on this forum, in this thread: https://community.powerbi.com/t5/Power-Query/Create-a-table-of-values-based-on-two-dates/m-p/819170#...

I attempted Phil's periodic billing measure but it timed out the file as the data table is just over 10m records consisting of orders' item level details, below is mine just attempting to create the measure on my data before trying to modify it for my calculated wkly amount.

Periodic Billing Attempt:

PeriodicBilling = 
VAR tmpCalendar = ADDCOLUMNS(key_Calendar,"Mo",key_Calendar[MonthNum],"Yr",key_Calendar[Year],"MonthYr",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS(tbl_Orders_DatesDays,"MonthYearBegin",VALUE(YEAR(tbl_Orders_DatesDays[BILLFROM_DATE]) & FORMAT(MONTH(tbl_Orders_DatesDays[BILLFROM_DATE]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR(tbl_Orders_DatesDays[BILLTO_DATE]) & FORMAT(MONTH(tbl_Orders_DatesDays[BILLTO_DATE]),"0#")))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Yr],[Mo],[MonthYr])
        ),
        [Yr] >= [MonthYearBegin] &&
        [MonthYr] <= [MonthYearEnd]
    ),
    "Yr",[Yr],
    "Mo",[Mo],
    "Amount",[Total Line Total]
)
RETURN SUMX(tmpTable,[Amount])

Suggestions or ideas?

Thank you kindly.

Let me know if anyone needs additional info or some sample data.

[v-frfei-msft]

Hi @DataUsurper ,

Please try the optimized way.

Table = 
VAR a =
    CALENDARAUTO ()
VAR b =
    SELECTCOLUMNS (
        'Order',
        "id", 'Order'[MAIN_ORDER],
        "product", 'Order'[MAINCODE]
    )
VAR k =
    ADDCOLUMNS (
        FILTER ( CROSSJOIN ( a, b ), WEEKDAY ( [Date], 2 ) = 1 ),
        "filter",
        VAR ship =
            CALCULATE (
                MAX ( 'date days'[SHIP_DATE] ),
                FILTER ( 'date days', 'date days'[ORDERID] = [id] )
            ) - 7
        VAR returndate =
            CALCULATE (
                MAX ( 'date days'[RETURN_DATE] ),
                FILTER ( 'date days', 'date days'[ORDERID] = [id] )
            )
        RETURN
            IF ( [Date] >= ship && [Date] <= returndate, 1, 0 )
    )
RETURN
    FILTER ( k, [filter] = 1 )

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

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

Pbix as attached.

[DataUsurper]

Good Morning Frank @v-frfei-msft! and thank you again for jumping in this exercise.

Is it possible to create this as a measure rather than a table? I apologize for not being clear about that in the OP, creating a table produces too many records (66 million, housed in Access since I'm unable to create a database file or whatever that is called).

[DataUsurper]

Friendly morning bump

[DataUsurper]

Afternoon bump