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
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
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.
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.
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.
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
