Visualize Backlog and Net Sales variation overtime

[Dante130]

FYI: first time posting, let me know if unclear and I will edit.

I created this small dataset to reply the situation I am having issue with in a bigger model.

2 fact tables:

Sales Order Line =Table showing the Sales log: Scheduled delivery + Amount per Sales Order

Sales Order Invoice Line = Table showing the Invoice log: Invoice date + Amount per Sales order

Here are the tables:

Goal:

Visualize the evolution of the net sales (Sum of Invoices) + backlog (Open Sales Order lines) overtime

Ex: Focus on SO1 + Use a slicer to change the date:

If: view date >= 2020/07/15
-> SO1 is fully invoice (Net Sales = 100 = 50 in Mai +50 in July)
-> SO1 Backlog = 0 : Backlog = Sales - Total past Net Sales = 100 - (50+50)

If:  2020/5/15 <= view date < 2020/7/15
-> SO1 is partialy invoice (Net Sales = 50 in Mai -> Because invoice date = 2020/5/15 )
-> SO1 backlog =50 in July : Backlog = Sales - Total Past Net Sales = 100 in July - 50

If:  2020/3/01 <= view date < 2020/5/15
-> SO1 is not invoiced : Net Sales = 0
-> SO1 backlog =100 in July : Backlog = Sales - Total Past Net Sales = 100 in July - 0

If:  view date < 2020/3/01
SO1 wasn't created: Net Sales = 0 & Backlog = 0

Model and Formula I tested:

Net Sales = CALCULATE(SUM(SO_Invoice[Amount]),USERELATIONSHIP(Sales_Order[Sales Order lines],SO_Invoice[Sales Order lines]),FILTER(SO_Invoice,SO_Invoice[Invoice date]<=MAX(Sec_Calendar[Date])))

Sales = CALCULATE(SUM(Sales_Order[Amount]),USERELATIONSHIP(Main_Calendar[Date],Sales_Order[Scheduled delivery date]))

Backlog = Sales -  Net Sales

    -> I tried lot of different backlog formula to try to unfilter the calendar in the calculation, but I am never getting the expected result.

This screenshot show my current result with those formula: (view date is after 2020/7/15, so the SO1 is fully Invoiced)

     -> The Net Sales is correct

      -> The Backlog is wrong: It should be at 0 for every month (because when the view date is after 2020/7/15, SO1 is fully invoiced: Sales - Total Net Sales = 100 - 50 - 50 = 0)

-> Backlog should be at 0 here.

I am also joining the PowerBi file (link expire on 2020/20/12) : WeTransfer Link 

[Dante130]

It works thanks !!!

[v-rzhou-msft]

Hi @Dante130 

In your screenshot, the date you selected may be later or equal to 2020/07/15.

Do you mean that when date >= 2020/07/15, Month 5 show -50 (50-100), Month 7 show(50+50 -100) = 0?

What should the result in backlog return if your date is between 2020/03/01 and 2020/07/15 or if the date is before 2020/03/01?

Please show me more details about the result you require.

Best Regards,

Rico Zhou

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

[Dante130]

Thank you for your reply, I edited my post to try to make it a bit clearer.

And I will try to develop a bit here as well:
So we just focus on SO1 for now.
If view date >= 2020/07/15
-> SO1 is fully invoice (Net Sales = 100 = 50 in Mai +50 in July)
-> SO1 Backlog = 0 : Backlog = Sales - Net Sales = 100 - (50+50)
If:  2020/5/15 <= view date < 2020/7/15
-> SO1 is partialy invoice (Net Sales = 50 in Mai -> Because invoice date = 2020/5/15 )
-> SO1 backlog = 50 in July (Because Sales - total past Net Sales = 100 in July - 50)
If:  2020/3/01 <= view date < 2020/5/15
-> SO1 is not at all invoiced: Net Sales = 0
-> SO1 Backlog = 100 in July
If:  view date < 2020/3/01
-> SO1 isn't created: Net Sales and Backlog are null

[v-rzhou-msft]

Hi @Dante130 

You can try my measure.

Measure:

Backkog - test 4 = 
VAR _SelDate =
    MAX ( Sec_Calendar[Date] )
VAR _YearMonth =
    YEAR ( MAX ( Sales_Order[Scheduled delivery date] ) ) * 100
        + MONTH ( MAX ( Sales_Order[Scheduled delivery date] ) )
VAR _NetSale =
    SUMX (
        FILTER (
            ALL ( SO_Invoice ),
            SO_Invoice[Invoice date] <= _SelDate
                && SO_Invoice[Sales Order lines] = SELECTEDVALUE ( Sales_Order[Sales Order lines] )
        ),
        SO_Invoice[Amount]
    )
VAR _Result = [Sales] - _NetSale
RETURN
    IF (
        MAX ( Main_Calendar[YearMonth] ) = _YearMonth,
        _Result
    )

Result is as below.

Ex. SO1, it will show backlog in July.

Selectdate = 2020/08/10 >= 2020/07/15 Result in July =100-50-50 = 0.

Selectdate = 2020/05/31 between 2020/05/15and 2020/07/15 Result in July =100-50.

Selectdate = 2020/04/13 before 2020/05/15 Result in July =100.

Selectdate = 2020/02/22before 2020/03/01 Result in July = null.

Best Regards,

Rico Zhou

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

[Dante130]

Hi @v-rzhou-msft ,
Thank you for your measure. It does work as expected when selecting one SO (SO1, SO2, SO3 or SO4).

But when we select 2 or more SO at the same time, it doesn't work.  I tried to modify your measure to take this into account, but no success on my side yet.
Any idea ?

[v-rzhou-msft · Accepted solution]

Hi @Dante130 

I think you may need to change your relationship between your tables to achieve your goal.

Measures:

Sales 1223 = 
VAR SelectedDate = MAX(Sec_Calendar[Date])
return
CALCULATE(SUM(Sales_Order[Amount]),Sales_Order[SO Created date]<=SelectedDate)

Measure 1223 = 
VAR _SelDate =
    MAX ( Sec_Calendar[Date] )
VAR _YearMonth =
    YEAR ( MAX ( Sales_Order[Scheduled delivery date] ) ) * 100
        + MONTH ( MAX ( Sales_Order[Scheduled delivery date] ) )
VAR _NetSale =
    SUMX (
        FILTER (
            ALL ( SO_Invoice ),
            SO_Invoice[Invoice date] <= _SelDate
                && SO_Invoice[Sales Order lines] in VALUES( Sales_Order[Sales Order lines] )
        ),
        SO_Invoice[Amount]
    )
VAR _Result = [Sales 1223] - _NetSale
VAR OutPut = 
    IF (
        MAX ( Main_Calendar[YearMonth] ) = _YearMonth,
        _Result
    )
RETURN OutPut

Result is as below.

Due to SO_invoice table doesn't contain SO4, so month 10 will show 100 if date>= create date 2020/06/01.

Date = 2020/05/15 select SO1 and SO2.

If you change your relationship you may need to update or rebuild your other measures.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

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