How to replicated this countifs in PowerBI

[johnbird]

Hello, 

I have a table below with a list of ID and dates. A [BindingID] will be repeted for each [ReportingPeriod] if it has been picked in that month. The Binding ID will only apprea once for each month. I want to  replicate the countifs formula I use in Excel in this table in PowerBI "=COUNTIFS($A$2:$A$586,A2,$C$2:$C$586,D2)"

So far I have this DAX function but it does not return anything. "

Issue_LastMonth = CALCULATE(COUNTA(dq_issues_tbl[BindingID]),FILTER(dq_issues_tbl,dq_issues_tbl[BindingID] = dq_issues_tbl[BindingID] && FORMAT(dq_issues_tbl[ReportingPeriod],"DD/MM/YYYY") = FORMAT(dq_issues_tbl[LastMonth],"DD/MM/YYYY")))"

Is there a way this can be achiveved within PowerBI.

BindingID	DQItemID	ReportingPeriod	Last Month	Issue Last Month
68	41	29/02/2024	31/01/2024	1
143	96	29/02/2024	31/01/2024	1
196	129	29/02/2024	31/01/2024	1
257	167	29/02/2024	31/01/2024	1
298	188	29/02/2024	31/01/2024	1
432	290	29/02/2024	31/01/2024	1
434	292	29/02/2024	31/01/2024	1
449	709	29/02/2024	31/01/2024	0
452	328	29/02/2024	31/01/2024	0
461	310	29/02/2024	31/01/2024	1
477	548	29/02/2024	31/01/2024	1
479	549	29/02/2024	31/01/2024	1
512	329	29/02/2024	31/01/2024	0
555	710	29/02/2024	31/01/2024	0
686	552	29/02/2024	31/01/2024	1
760	331	29/02/2024	31/01/2024	0
806	714	29/02/2024	31/01/2024	0
809	519	29/02/2024	31/01/2024	1

Thanks John

[aduguid]

Give this DAX measure a try

Issue_LastMonth = 
CALCULATE(
    COUNTROWS(dq_issues_tbl),
    FILTER(
        dq_issues_tbl,
        dq_issues_tbl[BindingID] = EARLIER(dq_issues_tbl[BindingID]) &&
        FORMAT(dq_issues_tbl[ReportingPeriod], "DD/MM/YYYY") = FORMAT(dq_issues_tbl[Last Month], "DD/MM/YYYY")
    )
)

[johnbird]

Thank you for the quick reply, unfortunatly it returns blank in the column.

[Greg_Deckler]

@johnbird Seems like that would be expected behavior. None of your rows have a ReportingPeriod that is equal to the Last Month so it's going to filter out all rows.

[johnbird · Accepted solution]

Ok so I decided to ask Microsoft Copilot and it actually worked. This is the solution:

Column =

VAR BindingID = dq_issues_tbl[BindingID]

VAR ReportingPeriod = FORMAT(dq_issues_tbl[LastMonth],"DD/MM/YYYY")

RETURN

    COUNTROWS(

        FILTER(

            ALL(dq_issues_tbl),

            dq_issues_tbl[BindingID] = BindingID &&

            FORMAT(dq_issues_tbl[ReportingPeriod],"DD/MM/YYYY") = ReportingPeriod

        )

    )

Thank for taking a look, Cheers John