cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ilyasss
Helper II
Helper II

Filter issue - Date aggregation

Hello guys, 

 

I'm facing an interesting problem.  

 

Basically, I created some measures to calculate if a Purchase order (PO) has been received on time or not. To do this, the measure ( datediff) calculates the difference in days between the expected date (ETA) and the effective receipt date. 

 

Nevertheless, each PO contains different products and each of them can be received at different time. In some case a PO can received a part of the product on time and the other part with a delay. In this case I would like to count this PO as delayed. 

 

To prevent this situation the datediff function will always calculate the difference between the minimum expected date and the maximum receipt date as you can see below : 

 

 

 

Date Diff  = 
DATEDIFF(MIN('Order'[expected receipt date]),LASTDATE('Receipt table'[Receipt Date]),DAY)

 

 

 

To calculate if a PO has been received on time : 

 

 

 

PO on Time = CALCULATE([Count Distinct PO],
    FILTER('Receipt table',([Date Diff]<15)),
    FILTER('Receipt table',([Date Diff]>=-14)))

 

 

 

To calculate if a PO has been received with a delay : 

 

 

 

PO delayed = CALCULATE([Count Distinct PO],
    FILTER('Orders ',([Date Diff]>=15)))

 

 

 

This works perfectly when there is no aggregation as you can see below ( it's one PO with 3 products) : 

Power Bi forum 1.jpg

But when I "aggregate" the data by PO, there is an issue as you can see below. The PO is counted as on time and delayed at the same time whereas it should be count only as delayed.

 

PBI forum 2.jpg

 

The behavior of the date diff function is good because it takes the highest diff in days (16), nevertheless, the PO on time doesn't react has expected because it counts the PO as on time whereas the datediff result is 16 and the function is setup to count as on time only if the date is <15 and >=-14. 

 

I get the impression that the aggregation I have done is implicit through the table and I should explicitly aggregate the data in my measure. Therefore my questions are : 

 

- Why my PO on time function does't take the result 16 to avoid to count it as a PO on time ? 

- What do you recommend to fix the issu and group by the datediff result by PO ? 

 

Many thanks for your help, 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi @Icey , 

 

Thank you, please find the file attached and the results in PO on time 3 and PO delayed 3. 

 

Let me know if you have any suggestion to improve it,  

 

Regards, 

View solution in original post

7 REPLIES 7
Icey
Community Support
Community Support

Hi @ilyasss ,

 

- Why my PO on time function does't take the result 16 to avoid to count it as a PO on time ? 

 

PBI forum 2.jpg

 


In the above table visual, [Date Diff (ETA, Receipt date)] is calculated based on the minimum ETA and the maximum Receipt date of the designated Purchase order. So, [Date Diff (ETA, Receipt date)] = 16 is not the result of the last row of records in your table visual. 

And for total values of [PO on time] and [PO delayed] are calculated based on the distincted count of Purchase order.

 

I create a simple example based on the data you provided to explain clearly:

datediff.PNG

- What do you recommend to fix the issu and group by the datediff result by PO ? 

 

Try to create another two measures based on your measures.

 

PO on time 2 = IF([PO on Time]=1&&[PO delayed]=BLANK(),1)
PO delayed 2 = IF([PO delayed]=1,1)

 

edit.PNG

 

 

BTW, .pbix file attached.

 

 

 

Best Regards,

Icey

 

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

Hello @Icey , 

 

Thank you for your very detailed explaination which helped a lot to understand the logic. 

 

Nevertheless, the solution proposed doesn't fit because both of the POs should be delayed and with the solution there is only one. 

 

Finally I created a summarize table to group by the Purchase order by date diff and use this date to make the calculcation and as you can see below, now both of the Po are counted as delayed and zero on time : 

PBI forum 4.jpg

 

Many thanks for your help, 

Icey
Community Support
Community Support

Hi @ilyasss ,

 

Glad to hear that you have solved the problem. Are you happy to share your solution based on the .pbix I uploaded before?

 

In addition, the expression of [PO delayed 2] in my previous reply needs to be modified like below. I have edited it in my previous reply.

PO delayed 2 = IF([PO delayed]=1,1)

 

 

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 @Icey , 

 

Thank you, please find the file attached and the results in PO on time 3 and PO delayed 3. 

 

Let me know if you have any suggestion to improve it,  

 

Regards, 

Icey
Community Support
Community Support

Hi @ilyasss ,

 

I have no access to click "I agree". Please use other tools, such as OneDrive for Business. Thank you!

wetransfer.PNG

 

 

Best Regards,

Icey

amitchandak
Super User
Super User

@ilyasss , In think you need to force a row context. refer to how it has been done in this blog.

 

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Hi @amitchandak ,

 

Thank you for the suggestion, I created a table with summarize column function to fix my issue.  

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors