Forum Discussion

Thundercat's avatar
Thundercat
Regular Visitor
9 years ago
Solved

Count Filtered by DatesBetween

Hello!   Very new to this, not a great deal of DAX experience but some basic java/c#!   Im trying to analyse delivery on time data, i can see the % of on time deliveries (for all of time) with th...
Need Help
Tutorial Requests
  • Thundercat's avatar
    Thundercat
    9 years ago

    After trawling the web for a few hours i found the solution on this very site:

     

    Get how many days late-

     

    Days Late = SWITCH(
    TRUE(),
    'Delivery_list_With_Parts'[Date Sent]<'Delivery_list_With_Parts'[SO Delivery Date],-1*DATEDIFF('Delivery_list_With_Parts'[Date Sent],'Delivery_list_With_Parts'[SO Delivery Date],DAY),
    'Delivery_list_With_Parts'[Date Sent]>'Delivery_list_With_Parts'[SO Delivery Date], DATEDIFF('Delivery_list_With_Parts'[SO Delivery Date], 'Delivery_list_With_Parts'[Date Sent],DAY),
    0)

     

    Organise into coloumbs

     

    On Time = IF(AND(Delivery_list_With_Parts[Days Late]>-1,Delivery_list_With_Parts[Days Late]<1),1,0)

    1-3 Days Late = IF(AND(Delivery_list_With_Parts[Days Late]>0,Delivery_list_With_Parts[Days Late]<3),1,0)

    4-7 Days Late = IF(AND(Delivery_list_With_Parts[Days Late]>3,Delivery_list_With_Parts[Days Late]<8),1,0)

     

    Measure % of on time deliveries 

     

    Deliveries On Time = (SUM (Delivery_list_With_Parts[On Time])+
    SUM (Delivery_list_With_Parts[Somehow Early])) /
    (SUM (Delivery_list_With_Parts[1-3 Days Late])+
    SUM (Delivery_list_With_Parts[4-7 Days Late])+
    SUM (Delivery_list_With_Parts[8-14 Days Late])+
    SUM (Delivery_list_With_Parts[15-30 Days Late])+
    SUM (Delivery_list_With_Parts[30+ Days Late]))

MattAllington's avatar
MattAllington
Community Champion
9 years ago

DAX is a very powerful language.  The Power Pivot engine uses a 2 step approach.  First filter the data and then complete a formula evaluation.  From what I can see (it is not actually clear) you may be relying on step 2 without properly leveraging step 1.  The only way to tell is if you can share your raw data design.  Can you explain that?

  • Thundercat's avatar
    Thundercat
    Regular Visitor
    9 years ago

    After trawling the web for a few hours i found the solution on this very site:

     

    Get how many days late-

     

    Days Late = SWITCH(
    TRUE(),
    'Delivery_list_With_Parts'[Date Sent]<'Delivery_list_With_Parts'[SO Delivery Date],-1*DATEDIFF('Delivery_list_With_Parts'[Date Sent],'Delivery_list_With_Parts'[SO Delivery Date],DAY),
    'Delivery_list_With_Parts'[Date Sent]>'Delivery_list_With_Parts'[SO Delivery Date], DATEDIFF('Delivery_list_With_Parts'[SO Delivery Date], 'Delivery_list_With_Parts'[Date Sent],DAY),
    0)

     

    Organise into coloumbs

     

    On Time = IF(AND(Delivery_list_With_Parts[Days Late]>-1,Delivery_list_With_Parts[Days Late]<1),1,0)

    1-3 Days Late = IF(AND(Delivery_list_With_Parts[Days Late]>0,Delivery_list_With_Parts[Days Late]<3),1,0)

    4-7 Days Late = IF(AND(Delivery_list_With_Parts[Days Late]>3,Delivery_list_With_Parts[Days Late]<8),1,0)

     

    Measure % of on time deliveries 

     

    Deliveries On Time = (SUM (Delivery_list_With_Parts[On Time])+
    SUM (Delivery_list_With_Parts[Somehow Early])) /
    (SUM (Delivery_list_With_Parts[1-3 Days Late])+
    SUM (Delivery_list_With_Parts[4-7 Days Late])+
    SUM (Delivery_list_With_Parts[8-14 Days Late])+
    SUM (Delivery_list_With_Parts[15-30 Days Late])+
    SUM (Delivery_list_With_Parts[30+ Days Late]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Thundercat,

      Glad to hear that you have solved the issue, you can accept your reply as solution, that way, other community members could easily find the solution when they get same issues.


      Thanks,
      Lydia Zhang