Forum Discussion
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 up date. I want to write a formula that essentially is a countif funtion. I want it to count the number of rows that match the ID of that row and have an entry date equal to or greater than the follow up date. I have tried numerous formulas with Calculate and Countrows but I can't figure out how to get this to come out. Any help would be appreciated.
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.
3 Replies
AnkitBISolution Sage
Share Sample data for your table or a copy of your pbix file with dataI 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.
