Calculating Daily Sales From Time Window

[kyleb350]

I am tasked to see if I can calculate past sales totals from when sales orders were in open status. The challenge is that all these orders are obviously closed, but I do have two dates on the orders that serve as a time window of when the order was open. Can I populate a date/calendar table (call it DailyOpenSales) where I can go through all orders and accumulate sales by day? 

SalesOrderHeader

OrderNo

DateCreated

InvoicedDate

1

|

*

Sales Order Detail

OrderNo

ItemCode

Amount

The logic to populate the daily sales would be something along the lines:

if SalesOrderHeader.DateCreated <= DailyOpenSales.Date && DailyOpenSales.Date < SalesOrderHeader.InvoicedDate,

DailyOpenSales.Sales += SalesOrderDetail.Amount

This sounds very resource intensive to go through all order lines for each day, but I can't think of any other way.

[v-yanjiang-msft · Accepted solution]

Hi @kyleb350 ,

According to your description, I create a sample.

This is SalesOrderHeader table.

This is Sales Order Detail table. There are relationship between this table and SalesOrderHeader table.

This is DailyOpenSales table.

There are two open days in the sample, for the first open day 3/4/2021, there are two orders compliant with computing standards, they are 0002 and 0003.

For the second open day 5/4/2021, there are three orders compliant with computing standards, they are 0003, 0004 and 0005.

You can see the order 0003 compliant with two open days at the same time, to avoid double counting, I let it count on the smallest open date 3/4/2021.

Here's my solution.

1. Create a calculated column in the SalesOrderHeader table.

Amount Column = RELATED('Sales Order Detail'[Amount])

2. Create a calculated column in the DailyOpenSales table.

Rank = RANKX('DailyOpenSales','DailyOpenSales'[Date],,ASC,Dense)

3. Create another calculated column in the DailyOpenSales table.

Total Sales = 
SUMX (
    FILTER (
        ALL ( 'SalesOrderHeader' ),
        'SalesOrderHeader'[DateCreated] <= 'DailyOpenSales'[Date]
            && 'SalesOrderHeader'[InvoicedDate] > 'DailyOpenSales'[Date]
            && 'SalesOrderHeader'[DateCreated]
                > MAXX (
                    FILTER (
                        ALL ( 'DailyOpenSales' ),
                        'DailyOpenSales'[Rank]
                            = EARLIER ( 'DailyOpenSales'[Rank] ) - 1
                    ),
                    'DailyOpenSales'[Date]
                )
    ),
    'SalesOrderHeader'[Amount Column]
) * 'DailyOpenSales'[Sales]

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[littlemojopuppy]

Hi @kyleb350 can you provide some sample data to work with?