topic Re: Pipeline Calculation (daily total Orders $ from the last business day + today total Invoice $ in DAX Commands and Tips

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

LogixAdex — Tue, 16 Nov 2021 08:17:18 GMT

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?

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

Icey — Fri, 19 Nov 2021 03:11:39 GMT

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.

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

LogixAdex — Tue, 23 Nov 2021 06:45:50 GMT

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

Thanks in advance

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

Icey — Wed, 24 Nov 2021 03:09:00 GMT

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

Best Regards,

Icey

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

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

LogixAdex — Wed, 24 Nov 2021 20:11:06 GMT

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/15

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

LogixAdex — Sat, 27 Nov 2021 12:45:30 GMT

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

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

Icey — Thu, 02 Dec 2021 05:36:47 GMT

Hi LogixAdex ,

Sorry to reply late.

Do you mean you want something like so?

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.

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

LogixAdex — Mon, 07 Feb 2022 06:46:04 GMT

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

Actual source (OData.Feed) that needs to be part of the report

Using the same relationship

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