Forum

countifs or sumprod...
 
Notifications
Clear all

countifs or sumproduct in Power Query or Dax

6 Posts
3 Users
0 Reactions
900 Views
(@reggieneo)
Posts: 4
Active Member
Topic starter
 

Hi All,
How can I do Countifs or Sumproduct of dates in power query, dax/power pivot  ?

I have multiple columns with 2 dates columns.
I need to count the rows that meets the 2 dates criteria of status.

Criteria and objective: Count the rows if reported date is before status date; and other criteria is the current row of status date.

my data model has date table.

My challenge is getting the current row in Status date as criteria. Please see the sample data for more reference.

Reported Date Status Date Countifs Results
6/1/2019 6/10/2019 =COUNTIFS($A$2:$A$20,"<"&B2,$B$2:$B$20,B2) 2
6/2/2019 6/4/2019 =COUNTIFS($A$2:$A$20,"<"&B3,$B$2:$B$20,B3) 1
6/2/2019 6/3/2019 =COUNTIFS($A$2:$A$20,"<"&B4,$B$2:$B$20,B4) 1
6/6/2019 6/10/2019 =COUNTIFS($A$2:$A$20,"<"&B5,$B$2:$B$20,B5) 2
6/6/2019 6/6/2019 =COUNTIFS($A$2:$A$20,"<"&B6,$B$2:$B$20,B6) 0

 

Reported Date  Status Date Sumproduct Result
6/1/2019 6/10/2019 =SUMPRODUCT(($K$2:$K$7<L2)*($L$2:$L$7=L2)) 2
6/2/2019 6/4/2019 =SUMPRODUCT(($K$2:$K$7<L3)*($L$2:$L$7=L3)) 1
6/2/2019 6/3/2019 =SUMPRODUCT(($K$2:$K$7<L4)*($L$2:$L$7=L4)) 1
6/6/2019 6/10/2019 =SUMPRODUCT(($K$2:$K$7<L5)*($L$2:$L$7=L5)) 2
6/6/2019 6/6/2019 =SUMPRODUCT(($K$2:$K$7<L6)*($L$2:$L$7=L6)) 0

much thanks

 
Posted : 09/09/2019 5:12 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Mario,

Welcome to our forum! This is probably a job for Power Pivot, but it's difficult to tell from the data above. Please provide a sample Excel file with your data model if possible. 

Thanks,

Mynda

 
Posted : 09/09/2019 6:07 pm
(@reggieneo)
Posts: 4
Active Member
Topic starter
 

Hi Mynda,

Much thanks for your reply. i have working on it for few days now. unable to crack the code.

please see sample file attached.

 
Posted : 10/09/2019 7:34 am
(@catalinb)
Posts: 1937
Member Admin
 

Hi Mario,

You should add a new column, here is the complete step:

NewColumn = Table.AddColumn(#"Changed Type", "CountIf", (x)=> Table.SelectRows(#"Changed Type",each x[#"Reported Date "] < [Status Date] and x[Status Date]=[Status Date]))

The new column will contain tables with only the matching records, you just have to expand this column, but choose aggregate instead of expand.

You will get the same countif functionality.

 
Posted : 11/09/2019 12:27 am
(@reggieneo)
Posts: 4
Active Member
Topic starter
 

Hi Catalin,

Its is working!

however it is really slow when I tried it.

I have about 400K rows and growing. I had to terminate the process when I fist applied as this its taking too long just to do the counts.

 

I then tested it with about 500 rows with just 2 columns and still took me an approximately  a minute to load in.  am I doing really wrong here? can you please suggest a better way to do it, faster.

 

appreciate it very much.

thanks.

 
Posted : 11/09/2019 10:50 am
(@reggieneo)
Posts: 4
Active Member
Topic starter
 

Hi Catalin,

I have noticed that it is counting the rows where the report date  is = status date.

example: report: 1/1,2019 , status: 1/1,2019 =1

answer should be 0.

the objective is : do not count if the report date is = to status date

please see test file.

thanks

 
Posted : 11/09/2019 11:24 am
Share: