calculate distinct months dynamically in each year and removing filters

[ScottWalter]

I have a table in which I have to calculate the average sales per year and per customer and table is designed using different columns from different tables on which I have to remove filters except on invoicedate/year.

In that table I have an invoicedate column from which I have been calculating the distinct months in which invoices occur. I have calculated the distinct months through this logic below.

var months = COUNTROWS(GROUPBY(InvoiceLines, InvoiceLines[INVOICEDATE].[Year], InvoiceLines[INVOICEDATE].[MonthNo]))

and it returns 12 for 2021 and for 2022 it returns 3.

In this table I have to remove the filters on all the rows so that it return 12 in each row of 2021 and 3 for 2022.

What can be the daX expression for removing the filters on all rows present in the table except invoicedate/year?

Thank you

[Anonymous]

Hi @ScottWalter ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Otherwise, could you please provide the Fields pane setting of your matrix visual? And is the field AXPAPPROVEDCREDITLIMIT also from the table InvoiceLines? Do you want all rows in 2021 to show 12 and all rows in 2022 to show 3 as marked in the screenshot below?

Fields pane setting

Best Regards

[tamerj1 · Accepted solution]

Hi @ScottWalter 

you can try

var months =
CALCULATE (
    COUNTROWS (
        GROUPBY (
            InvoiceLines,
            InvoiceLines[INVOICEDATE].[Year],
            InvoiceLines[INVOICEDATE].[MonthNo]
        )
    ),
    REMOVEFILTERS (),
    VALUES ( InvoiceLines[INVOICEDATE].[Year] )
)

[littlemojopuppy]

@ScottWalter can you share your pbix?  Or a pbix with some mocked up data?  Difficult to do without having data to work with...