Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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
)
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
)
Thank you for your help.
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |