cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dante130
Regular Visitor

Visualize Backlog and Net Sales variation overtime

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:

Sales_order_line_table.png

Sales_order_Invoice_line_table.png

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)

SO1-2020-12-1.png
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

SO1-2020-7-14.png

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

SO1-2020-5-14.png

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

 

Model and Formula I tested:

Model.png

 

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_Issue.png-> Backlog should be at 0 here.

 

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

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @Dante130 

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

1.png

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.

2.png

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

3.png

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. 

View solution in original post

6 REPLIES 6
Dante130
Regular Visitor

It works thanks !!!

RicoZhou
Community Support
Community Support

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. 

 

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

RicoZhou
Community Support
Community Support

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.

1.png

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

2.png

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

3.png

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

4.png

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.

Hi @RicoZhou ,
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 ?

RicoZhou
Community Support
Community Support

Hi @Dante130 

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

1.png

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.

2.png

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

3.png

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. 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.