topic Re: Shipments and Open Order Horizon (Events in Time Scenario?) in DAX Commands and Tips

Shipments and Open Order Horizon (Events in Time Scenario?)

mwilliamsjr — Mon, 28 Feb 2022 18:12:20 GMT

Hello,

I am struggling to come up with the correct DAX calculation and am coming to the forums for some guidance.

Tables:

Sales: regular sales table showing historical sales and refreshed each week.
Backlog: table of open order snapshots (weekly, going back two years). Is refreshed each week at the same time of refresh as the Sales table.
Dates: Traditional Calendar table spanning contigous dates and includes additive columns such as WeekOfQuarter,Year-Qtr, etc.

Joins:

Sales[InvoiceDate]: Dates[Date] (many:1)
Backlog[EstShipDate]: Dates[Date] (many:1)

Measures:

Sales= Sum([SalesRevenue])
Backlog= Sum([BacklogRevenue])

What I would like to visualize is how the Open Orders grow 13 weeks from the start of each quarter, and how Sales+Backlog trend during the quarter, basically over a 26-week period. Finally, I would like to view prior quarters for their 26-week period to ultimately show trend quarter over quarter for the 26-week period.

I have prepared this chart in Excel with the data I have to give a sense of what I am looking for.

Any help will greatly be appreciated.

Re: Shipments and Open Order Horizon (Events in Time Scenario?)

mwilliamsjr — Mon, 28 Feb 2022 20:30:24 GMT

Hello,

I am struggling to come up with the correct DAX calculation and am coming to the forums for some guidance.

Tables:

Sales: regular sales table showing historical sales and refreshed each week.
Backlog: table of open order snapshots (weekly, going back two years). Is refreshed each week at the same time of refresh as the Sales table.
Dates: Traditional Calendar table spanning contigous dates and includes additive columns such as WeekOfQuarter,Year-Qtr, etc.

Joins:

Sales[InvoiceDate]: Dates[Date] (many:1)
Backlog[EstShipDate]: Dates[Date] (many:1)

Measures:

Sales= Sum([SalesRevenue])
Backlog= Sum([BacklogRevenue])

I have prepared this chart in Excel with the data I have to give a sense of what I am looking for.

Any help will greatly be appreciated.

Re: Shipments and Open Order Horizon (Events in Time Scenario?)

lbendlin — Tue, 01 Mar 2022 22:08:11 GMT

You are using the terms "Open Orders" and "Backlog" - are these synonymous in your case?

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Shipments and Open Order Horizon (Events in Time Scenario?)

amitchandak — Wed, 02 Mar 2022 06:36:04 GMT

mwilliamsjr ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Shipments and Open Order Horizon (Events in Time Scenario?)

mwilliamsjr — Wed, 02 Mar 2022 12:28:28 GMT

Hi and thank you for your response.

The table in the linked file is a subset of the data that is shown in the chart from my initial post. The chart is based on the total column. The Backlog and sales are two different tables, and the backlog is snapshotted each week and added to the Backlog table.

My original thought was to use the USERELATIONSHIP function using the DAX below, but I think this is half the equation.

Calculate([Rev | BiBa Commit], USERELATIONSHIP(dimDateInvoice[InvoiceDate], Backlog[WeekEnding]))

https://docs.google.com/file/d/1fI7F6uZsZ3oZTsE4aOIOQq02CCdMnilw/edit?usp=docslist_api&filetype=msexcel

Just in case you cannot get to the file, I am including the snapshot.

Re: Shipments and Open Order Horizon (Events in Time Scenario?)

Anonymous — Thu, 03 Mar 2022 09:44:19 GMT

Hi mwilliamsjr ,

I can't access your files because I don't have a Google account... You can refer the following thread to upload the file in the community. Please note that exclude any sensitive info in your sharing file.

How to upload PBI in Community

Do you want to create the following line chart by quarters? What's the calculation logic of these line values?

Best Regards