Dynamic Sales orders and backlog

[Tom_Burns]

Hi all,

Fairly amateur Power BI user here!  I have tried to do this in as many ways as my brain can manage but am completely stuck - massively appreciate anyone's advice.

I have a data set, to keep things simple, that just includes these columns:

• Order Week Reference
• Order Number
• Value
• Invoiced Week Reference

Example

Order Week Ref	Details	Value	Invoiced Week
1	Order 1	50,000.00	3
1	Order 2	60,000.00	2
2	Order 3	20,000.00	3
2	Order 4	25,000.00	5
3	Order 5	55,000.00	4
3	Order 6	60,000.00	5
4	Order 7	80,000.00
4	Order 8	10,000.00

Each week, i have to update a sales order report (these lines get added and tagged with the latest order week reference)

It can take 1-4 weeks lead time for an order to be shipped out and then invoiced.  So at any point in time, my data set will include backlog (which will be orders not shipped).

I am trying to work out how to show the following tables, for a selection on a slicer for the order week reference

• New orders placed in the week
• Current backlog 

New orders is the easy bit, I get stuck with the backlog.  I have tried all sorts of ways to do it but can't seem to figure it out.  

For clarity, if the week selction is week 1, you would expect to see order 1 and order 2 in both tables.

If the selection week is week 4, you would expect to see order 7 and 8 in the new orders table and then orders 4,6,7 and 8 in the backlog table (since they are either not yet invoiced or invoiced later than week 4 selected).

I have tried all sorts of ways of using different date tables, making the date table relationships inactive and using the 'userelationship' function, have tried using calculatetable etc as well....as i said, I am an amateur and have probably missed something obvious.  Please help!

If anyone can figure it out or give me some tips, I'd be eternally grateful.

Tom

[WinterMist]

@Tom_Burns 

This reminds me of a problem that @DataInsights helped me with some time ago.

Your table visual example states:

IF WeekSelected = 4 THEN 

   - BackLog Orders: Orders 4, 6, 7 & 8

NOTE: As you know, this outcome includes rows which are OUTSIDE of the FC (filter context) of the slicer.  And this is what causes the challenge.

To make this happen, I interpreted your BackLog logic as follows:

   IF (

      [Invoiced Week] > [WeekSelected]) OR

      [Invoiced Week] IS NULL

   )

   THEN it's a BackLog record

As you stated, when you select 'Data 1'[Order Week Ref] in the slicer, it always removes records that you want to keep in the BackLog table.  I remember your frustration very well.

To work around this, take the following steps:

1) Create a new table which is a copy of the first table.  

NOTE: There is no relationship between these 2 tables.

2) Make sure that each table visual pulls from the correct model table:

   - "New Orders" table visual must pull all its fields from 'Data 1' model table (so that the slicer affects it).

   - "BackLog" table visual must pull all its fields from 'Data 2' model table ( so that the slicer will NOT affect it).

3) Create a measure that gets the WeekSelected from the 'Data 1'[Order Week Ref] slicer:

4) Create a measure that identifies the rows in 'Data 2' that meet the above interpreted logic for BackLog records:

5) Add both measures to the Backlog table visual to test it:

Test Results: Working according to interpreted logic.

6) Select the BackLog table visual and change the filter on the measure for the visual to only include where 02 IsBackLog = 1.

Once the filter is applied, now only the desired records appear in the BackLog table.

I hope this is helpful to you.

Regards,

Nathan

[amitchandak]

@Tom_Burns , Do you have one table or 2 tables? If you have two tables, you can join both with a common date table and create measures for new and backlog.

If you have only one table backlog logic is not very clear

for new logic

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

Common tables

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

If this does not help
Can you share sample data and sample output in table format?