Forum Discussion
Converting Excel Countif
Hello Team,
I am new to Power BI.
I trying to convert the excel formula to DAX.
Count Cases Last 31 =COUNTIFS(A:A,$A2,C:C,$C2,B:B,">="&$B2-30)
Sample data is below:
| Cust Site ID | Case Create DateTime | Case Type | Count Cases Last 31 |
| S12245 | 6/27/2021 10:18 | SH | 2 |
| S12245 | 6/28/2021 13:14 | WO | 12 |
| S12245 | 6/28/2021 19:32 | CRU | 2 |
| S12245 | 6/28/2021 19:32 | CRU | 2 |
| S12245 | 7/3/2021 20:53 | WO | 12 |
| S12245 | 7/18/2021 8:41 | WO | 12 |
| S12245 | 7/18/2021 8:45 | WO | 12 |
| S12245 | 7/22/2021 16:52 | WO | 12 |
| S12245 | 7/22/2021 16:54 | WO | 12 |
| S12245 | 7/27/2021 2:58 | WO | 12 |
| S12245 | 7/28/2021 16:28 | WO | 11 |
| S12245 | 7/29/2021 11:27 | WO | 11 |
| S12245 | 7/30/2021 16:06 | SH | 1 |
| S12245 | 8/1/2021 0:20 | WO | 11 |
| S12245 | 8/1/2021 22:28 | WO | 11 |
| S12245 | 8/9/2021 0:06 | WO | 10 |
There are lots of ways to do this. I'd suggest something like this as a calculated column:
CountIf = VAR CurrRowDate = Table1[Case Create Date Time] RETURN CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Cust Site ID], Table1[Case Type] ), Table1[Case Create Date Time] >= CurrRowDate - 30 )
2 Replies
There are lots of ways to do this. I'd suggest something like this as a calculated column:
CountIf = VAR CurrRowDate = Table1[Case Create Date Time] RETURN CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Cust Site ID], Table1[Case Type] ), Table1[Case Create Date Time] >= CurrRowDate - 30 )