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
jessie40
Frequent Visitor

Calculate a total for running dates and all dates prior.

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:

ordernumneedbydateshipdate
5763246/8/2022 
5763036/8/2022 
5762876/8/20226/8/2022
5705606/7/2022 
5705556/7/20226/8/2022
5705496/6/2022 

 

Results Table:

DateLate
6/8/20225
6/7/20223
6/6/20221

 

Any assistance would be great as I am new to this tool.

6 REPLIES 6
v-zhangti
Community Support
Community Support

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
    )
)

vzhangti_0-1656056304591.png

Table:

Table 2 = 
SUMMARIZE('Table','Table'[needbydate],'Table'[Late])

vzhangti_1-1656056347564.png

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. 

 

NeedbyDateLate
6/24/2022390
6/23/2022362
6/22/2022350
6/21/2022328
6/18/2022282
6/17/2022279
6/16/2022265
6/15/2022253
6/14/2022225
6/11/2022211
6/10/2022201
6/9/2022187
6/8/2022170
6/7/2022159
6/4/2022142
6/3/2022136
6/2/2022129
6/1/2022124
5/31/2022116
5/28/2022112
5/27/2022111
5/26/202299
5/25/202291
5/24/202290

 

This is how the manual process looks. See how the Past Due Actual fluctuates up and down. 

Wk EndingPast Due - Actual
6-May517
13-May536
20-May560
27-May570
3-Jun645
10-Jun569
17-Jun529

 

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. 

Hi, @jessie40 

 

Can you provide sample files or more sample data?

 

Best Regards

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
Super User
Super User

@jessie40 

pls try this

Measure = CALCULATE(COUNTROWS('Table'),FILTER(all('Table'),'Table'[needbydate]<=max('Table'[needbydate])&&('Table'[shipdate]<'Table'[needbydate] || ISBLANK('Table'[shipdate]))))

1.PNG

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?





Did I answer your question? Mark my post as a solution!

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. 

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.