Forum Discussion
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 this formula
Average Year = COUNT (Delivery[Delivered on Time])/(COUNT (Delivery[1-3 Days Late])+COUNT(Delivery[4-7 Days Late])+COUNT(Delivery[8-14 Days Late])+COUNT(Delivery[15-30 Days Late])+COUNT(Delivery[30+ Days Late]) +COUNT(Delivery[Delivered on Time]))
I would like to do it for a month, or three months, so i tried this horrible formula
Average Month = (IF(DATESBETWEEN('Delivery'[1.Date Sent],10/2016,11/2016),COUNT(Delivery[Delivered on Time]),0)/
(IF(DATESBETWEEN('Delivery'[1.Date Sent],10/2016,11/2016),COUNT (Delivery[1-3 Days Late]),0)+
(IF(DATESBETWEEN('Delivery'[1.Date Sent],10/2016,11/2016),COUNT (Delivery[1-3 Days Late]),0)+
(IF(DATESBETWEEN('Delivery'[1.Date Sent],10/2016,11/2016),COUNT(Delivery[4-7 Days Late]),0)+
(IF(DATESBETWEEN('Delivery'[1.Date Sent],10/2016,11/2016),COUNT(Delivery[8-14 Days Late]),0)+
(IF(DATESBETWEEN('Delivery'[1.Date Sent],10/2016,11/2016),COUNT(Delivery[15-30 Days Late]),0)+
(IF(DATESBETWEEN('Delivery'[1.Date Sent],10/2016,11/2016),COUNT(Delivery[30+ Days Late]),0) +
(IF(DATESBETWEEN('Delivery'[1.Date Sent],10/2016,11/2016),COUNT(Delivery[Delivered on Time]),0))
Is it just me or do some DAX functions not wok in Power Bi?
Thanks in advance!
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]))
3 Replies
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?
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]))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
