Calculate a total for running dates and all dates prior.

[jessie40]

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.

[v-zhangti]

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.

[jessie40]

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. 

[v-zhangti]

Hi, @jessie40 

Can you provide sample files or more sample data?

Best Regards

[jessie40]

thanks all for your time. I found that my original query was missing data. Our developers fixed that issue and all is working! 

[ryan_mayu]

@jessie40 

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?

[jessie40]

@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.