Data Modeling question Or potential pattern

[sdmikejr]

Challenge: I have a table with actual sales and a table with backlog that gets updated weekly. Users want to see not only what sales was or is at any given time, they also want to see what the backlog was or is at that specific time. 

The Backlog Table:

The backlog table has a 2 date fields. The first date field is named Backlog Archive Date. and is used to filter the data on the most recent archive date, to ensure the backlog is not double-counted. The 2nd date field is named Est. Ship Date and is linked to the date table.

There are 2 new requirements from users:

1) The ability to look at a 'snapshot' of what sales revenues was for any time period, but also to understand for the filtered time period, what the backlog was as well.

2) The ability to understand what the backlog, as a % of total revenue was at the beginning of the month. (Obviously the % would be higher at the beginning of the month, and % of total would go down the further we got into the month.) The request seems like a value-add so I would at least like to entertain the idea.

If someone can point me in the right direction for at least 1 above, you'd be a rockstar for sure!!!

[jondeck24]

Assuming you said you have a Date table - meaning your Sales and Backlog tables are both related to this Date table?  If so then it should be easy - pull up a Card showing sales.  Then pull up another one showing the backlog.  Then add in a date filter so that the client can go from month to month looking at the metrics for both.  Pretty smiple but then you could break out sales by month in a chart so they have a more visual queue from which to look from to see how sales are doing on a monthly basis as well as the backlog. 

Your explanation isn't the clearest so taking a shot in the dark that this is what you are looking for in #1?

[sdmikejr]

Thank you for the reply to help resolve my challenge, but let me show you a visual and further explain.

The sales and backlog tables are connected to the date table by the Ship Date and Est Ship Date, respectively.

The backlog table is an actual 'archive' table that has the weekly backlog snapshot appended to it each week. It has a field called backlog archive date.  So I manually filter the table each week by filtering on the most recent archive date. 

If I didn't filter it and just let the data run free, then it will double-count all backlog that was in the prior week's archive that didn't ship. It may be clear to you if I point out that we receive orders in advance of 4-5 months before customers actually want the goods shipped. 

What I need is a way to make sure the backlog archive date moves in sync with the calendar table, but am not sure exactly where to begin.

[v-shex-msft · Accepted solution]

Hi @sdmikejr,

According to your description, you want to keep the records for the last seven days of the date table,right?

If it is a case, you can refer to below formula if it works for your requirement.

Last 7 Day Record= CALCULATETABLE(tbl_Backlog,FILTER(ALL(tbl_Backlog),tbl_Backlog[Est Ship Date] >=DATEADD(LASTDATE(ALL(tbl_Date[Date])),-7,DAY)&&tbl_Backlog[Est Ship Date] <=LASTDATE(ALL(tbl_Date[Decription]))))

Regards,

Xiaoxin Sheng