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

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