Active Member
September 8, 2019
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
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.
Active Member
September 8, 2019
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.
1 Guest(s)