Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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.
Thanks Icey
Here is a PBIX file with sample data:
https://personal.syncedtool.com/shares/file/pnPIqDG6eL7/
Thanks in advance
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.
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
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.
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
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
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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |