Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Icey
Community Support
6 years ago

Hi Anonymous ,

 

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

 

 

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:

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

 

 

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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 : 

     

    Many thanks for your help, 

    • Icey's avatar
      Icey
      Community Support
      6 years ago

      Hi Anonymous ,

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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,