How to get counts of records for a column based on two date columns

[kotlonarendra]

Input: 

	GuideApprovalRequestDate	GuideApprovalDate
P1272_NSTwa_N2NozzleSensorSetup_Rev2	7/1/2021 13:38	9/30/2021 15:47
P1272_NSTwa_WaferStrandingProcedure_Rev1	7/1/2021 13:55	9/30/2021 15:47
P1272_NSTwa_E3DashboardTrace_Rev1	7/1/2021 13:54	9/30/2021 15:47
P1272_NSTwa_VortexFlowmeterFlaring_Rev1	7/1/2021 13:34	9/30/2021 15:47
P1272_NSTwa_WaferUnStrandingProcedure_Rev1	7/1/2021 13:56	9/30/2021 15:47

Result: 

	Count GuideApprovalRequestDate	Count GuideApprovalDate
9/30/2021 15:47	0	5
7/1/2021 13:38	5	0

[v-chenwuz-msft · Accepted solution]

Hi @kotlonarendra ,

I don't understand why only one result of GuideApprovalRequestDate is kept in your result, and the others are removed.
This form needs a unique field, if not, then I will sort by GuideApprovalDate.

Unpivot this table.

And then get the most date of the current group via this code:

Column =
VAR _a =
    CALCULATE (
        MIN ( 'Table'[Value] ),
        FILTER (
            'Table',
            [GuideApprovalDate] = EARLIER ( 'Table'[GuideApprovalDate] )
                && [Attribute] = "Group.GuideApprovalRequestDate"
        )
    )
RETURN
    IF ( [Attribute] = "Group.GuideApprovalDate", [Value], _a )

At last, put this column in the Rows of matrix table and Attribute in the Columns.

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kotlonarendra]

@rajulshah