Forum Discussion
Solved
PowerPivot CountIf date in one row is after a date in another
I am using Power Query and Power Pivot to try and create a living report that can be edited. I have one column with a unique ID, another with the date the entry was made, and another with the follow ...
Hi asd2525
Create a new column
new column = VAR Max_each_id = CALCULATE ( MAX ( 'Table'[Entry Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) RETURN IF ( Max_each_id >= [Followup Date], 1, 0 )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AnkitBI
Solution Sage
Solution SageShare Sample data for your table or a copy of your pbix file with data
I am dealing with HIPPA data so I can't share but this is an example of what I want to be able to do with powerpivot.
The equation that I would use in a regular table would be =IF(COUNTIFS($D$2:$D$5,$D2,$A$2:$A$5,">="&$E2),"Yes","No")
I can't figure out how to get the same results with PowerPivot
| Date | Name | DOB | ID | Followup Date | Followup Occurred? |
| 8/18/2019 | John Doe | 1/1/1901 | John Doe367 | 9/18/2019 | Yes |
| 8/25/2019 | Jane Doe | 2/2/1902 | Jane Doe764 | 9/25/2019 | No |
| 9/3/2019 | John Smith | 3/3/1903 | John Smith1158 | 10/3/2019 | No |
| 9/18/2019 | John Doe | 1/1/1901 | John Doe367 | 10/18/2019 | No |
v-juanli-msftCommunity Support
Hi asd2525
Create a new column
new column = VAR Max_each_id = CALCULATE ( MAX ( 'Table'[Entry Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) RETURN IF ( Max_each_id >= [Followup Date], 1, 0 )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.