Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LogixAdex
Frequent Visitor

Pipeline Calculation (daily total Orders $ from the last business day + today total Invoice $

I need help to create a Pipeline report using the following:

-Invoicing happens the next business day, Like this:


Orders from Monday will be invoiced Tuesday
Orders from Tuesday will be invoiced Wednesday
Orders from Wednesday will be invoiced Thursday
Orders from Thursday will be invoiced Friday
and
Orders from Friday will be invoiced Monday

Pipeline is the sum of:
Sales Orders total $ amount from the last business day
+
Invoices total $ from today (Business day)

There are 2 tables (Sales Orders and Invoices) Both contain pretty much the same fields (Doc. #, Posting date, Customer, etc.)
There is a third table with a calendar where both table have a relationship based on the "Posting_Date"


How I can create a formula where i can accomodate the fridays Sales Orders with Monday Invoices?

 

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @LogixAdex ,

 

Sorry to reply late.

 

Do you mean you want something like so?

Icey_0-1638423272511.png

Pipeline 2 = 
VAR ThisDay_ =
    MAX ( 'Sales Orders'[Order Date] )
VAR NextWorkingDay_ =
    CALCULATE (
        MIN ( '0 - Calendar 00-25'[Date] ),
        '0 - Calendar 00-25'[Date] > ThisDay_,
        '0 - Calendar 00-25'[WeekNo] IN { 2, 3, 4, 5, 6 }
    )
VAR Sales_Orders_total =
    SUM ( 'Sales Orders'[Amount] )
VAR Invoices_total =
CALCULATE(
    SUM ( 'Invoices'[Amount] ),
        FILTER (
            ALLSELECTED ( '0 - Calendar 00-25'[Date] ),
            '0 - Calendar 00-25'[Date] = NextWorkingDay_
        )
    )
RETURN
    Sales_Orders_total + Invoices_total

 

Best Regards,

Icey

 

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

7 REPLIES 7
LogixAdex
Frequent Visitor

Thanks Icey
Here is a PBIX file with sample data:
https://personal.syncedtool.com/shares/file/pnPIqDG6eL7/ 

Thanks in advance

Icey
Community Support
Community Support

Hi @LogixAdex ,

 

Please check if this is what you want:

Pipeline = 
VAR ThisDay_ =
    MAX ( 'Sales Orders'[Order Date] )
VAR LastWorkingDay_ =
    CALCULATE (
        MAX ( '0 - Calendar 00-25'[Date] ),
        '0 - Calendar 00-25'[Date] < ThisDay_,
        '0 - Calendar 00-25'[WeekNo] IN { 2, 3, 4, 5, 6 }
    )
VAR Sales_Orders_total =
    CALCULATE (
        SUM ( 'Sales Orders'[Amount] ),
        FILTER (
            ALLSELECTED ( '0 - Calendar 00-25'[Date] ),
            '0 - Calendar 00-25'[Date] = LastWorkingDay_
        )
    )
VAR Invoices_total =
    SUM ( 'Invoices'[Amount] )
RETURN
    Sales_Orders_total + Invoices_total

pipeline.PNG

 

 

Best Regards,

Icey

 

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

Icey: I found what the issue is. Total sales should be as per this example on the picture 

 

Total sales (Pipeline) for last business day: Last business day Sales Orders + today's Invoices,

Examples:
1) Total sales for 11/12 = $ 68,144.82 now showing under 11/15
2) Total sales for 11/11 = $ 71,727.42 now showing under 11/12
Pipeline.png

Icey
Community Support
Community Support

Hi @LogixAdex ,

 

Sorry to reply late.

 

Do you mean you want something like so?

Icey_0-1638423272511.png

Pipeline 2 = 
VAR ThisDay_ =
    MAX ( 'Sales Orders'[Order Date] )
VAR NextWorkingDay_ =
    CALCULATE (
        MIN ( '0 - Calendar 00-25'[Date] ),
        '0 - Calendar 00-25'[Date] > ThisDay_,
        '0 - Calendar 00-25'[WeekNo] IN { 2, 3, 4, 5, 6 }
    )
VAR Sales_Orders_total =
    SUM ( 'Sales Orders'[Amount] )
VAR Invoices_total =
CALCULATE(
    SUM ( 'Invoices'[Amount] ),
        FILTER (
            ALLSELECTED ( '0 - Calendar 00-25'[Date] ),
            '0 - Calendar 00-25'[Date] = NextWorkingDay_
        )
    )
RETURN
    Sales_Orders_total + Invoices_total

 

Best Regards,

Icey

 

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

Hi. The solution provided worked with the provided resources however when I tried with the actual resource it displays the calendar and the sum plus the chossen date. Please see picture.


Source provide for testing and working with pbi
Error with Invoice 002.PNG

Actual source (OData.Feed) that needs to be part of the report
Error with Invoice 003.PNG
Using the same relationship
Error with Invoice 004.PNG

But when I try to use the same measure


0 -Invoices total $ from today (Business day) =
VAR ThisDay_ =
MAX ( 'SalesOrders'[Order Date] )
VAR NextWorkingDay_ =
CALCULATE (
MIN ( '0 - Calendar'[Date] ),
'0 - Calendar'[Date] > ThisDay_,
'0 - Calendar'[WeekNo] IN { 2, 3, 4, 5, 6 }
)
RETURN
CALCULATE(
SUM ( 'Invoices_2'[Amount]),
FILTER (
ALLSELECTED ( '0 - Calendar'[Date] ),
'0 - Calendar'[Date] = NextWorkingDay_
)
)
 
 
 
I get the following

Error with Invoice 001.PNG

Thanks Icey for your time and reply.
It is not getting the results that I'm expecting. Please look at the attached image.
Basically invoices are generated the next business day. As per the picture It should trow the Sales Orders Amount from 11/12  and invoices from 11/15SO Friday - Invoices MondaySO Friday - Invoices Monday

Icey
Community Support
Community Support

Hi @LogixAdex ,

 



How I can create a formula where i can accomodate the fridays Sales Orders with Monday Invoices?

 


You can use a "IsWorkingDay" column as another filter.

IsWorkingDay =
IF ( WEEKDAY ( 'Calendar'[Date], 1 ) IN { 2, 3, 4, 5, 6 }, 1, 0 )

 

If you need specific operations, please provide sample data.

 

Reference: 

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

 

 

Best Regards,

Icey

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors