Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi! I hope I explain this well.
I'm looking to sum the value of Orders that are late. A late order is one where the ship date is > need by date. I want to create a trended chart to see how many late order we had in prior days/months compared to current. How to I calculate something like this in Power BI?
If Need By Date > Ship By Date or if Ship By Date = Blank, then count as late.
Then have a date table with all dates and their total count of late orders for that date to include all prior dates.
Orders Table:
ordernum | needbydate | shipdate |
576324 | 6/8/2022 | |
576303 | 6/8/2022 | |
576287 | 6/8/2022 | 6/8/2022 |
570560 | 6/7/2022 | |
570555 | 6/7/2022 | 6/8/2022 |
570549 | 6/6/2022 |
Results Table:
Date | Late |
6/8/2022 | 5 |
6/7/2022 | 3 |
6/6/2022 | 1 |
Any assistance would be great as I am new to this tool.
Hi, @jessie40
You can try the following methods.
Column:
Is Late = IF([shipdate]=BLANK(),1,IF([shipdate]>[needbydate],1,0))
Late =
CALCULATE (
COUNT ( 'Table'[ordernum] ),
FILTER (
'Table',
[needbydate] <= EARLIER ( 'Table'[needbydate] )
&& [Is Late] = 1
)
)
Table:
Table 2 =
SUMMARIZE('Table','Table'[needbydate],'Table'[Late])
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I feel like you are on the right track. 6/8 should be 4 and not 5 like I originally stated...I appologize. On 6/8, order 570555 is no longer late, so should not be counted. Here is actual data using your method. I feel that the late values should be going down based on Orders we have shipped and up based on orders due.
NeedbyDate | Late |
6/24/2022 | 390 |
6/23/2022 | 362 |
6/22/2022 | 350 |
6/21/2022 | 328 |
6/18/2022 | 282 |
6/17/2022 | 279 |
6/16/2022 | 265 |
6/15/2022 | 253 |
6/14/2022 | 225 |
6/11/2022 | 211 |
6/10/2022 | 201 |
6/9/2022 | 187 |
6/8/2022 | 170 |
6/7/2022 | 159 |
6/4/2022 | 142 |
6/3/2022 | 136 |
6/2/2022 | 129 |
6/1/2022 | 124 |
5/31/2022 | 116 |
5/28/2022 | 112 |
5/27/2022 | 111 |
5/26/2022 | 99 |
5/25/2022 | 91 |
5/24/2022 | 90 |
This is how the manual process looks. See how the Past Due Actual fluctuates up and down.
Wk Ending | Past Due - Actual |
6-May | 517 |
13-May | 536 |
20-May | 560 |
27-May | 570 |
3-Jun | 645 |
10-Jun | 569 |
17-Jun | 529 |
I may not be explaining myself well. Each day, we should be counting all orders that are Late. Late = Ship date > Need By. Like we are creating a historical table.
thanks all for your time. I found that my original query was missing data. Our developers fixed that issue and all is working!
pls try this
Measure = CALCULATE(COUNTROWS('Table'),FILTER(all('Table'),'Table'[needbydate]<=max('Table'[needbydate])&&('Table'[shipdate]<'Table'[needbydate] || ISBLANK('Table'[shipdate]))))
why 6/8 got 5 records? the two records which have shipdate, both of them are bigger than or equal to need by date . Your logic is need by date > ship by date. Could you pls clarify your logic?
Proud to be a Super User!
@ryan_mayu I believe you are right, as on 6/8 the late order from 6/7 is no longer late, so 6/8 should be 4.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |