Number of years with more then 2 invoices per year

[PeterRodet]

Hello There, 

First of all, thank for your help. 

I am using powerpivot for the following analysis.

I would like to calculate the number of years (based on the transaction date) in which a client had more then 2 invoices in a year. The number of invoices should be calculated with distinctcount because if an invoice contains multiple products, the invoice number is repeated in the first column. Could anyone help me define a good measure?

This is +/- what my database looks like: 

Documentnr     Clientnr  Date         Product    Amount (€)

INV001-2018	ABC	1/03/2018	Egg	1
INV001-2018	ABC	1/03/2018	Pineapple	5
INV001-2018	ABC	1/03/2018	Salad	6
INV002-2018	DEF	5/04/2018	Peppers	3
INV003-2018	ABC	15/06/2018	Peppers	3
INV002-2018	DEF	5/04/2018	Salad	6
INV004-2018	DEF	16/10/2018	Egg	1
INV004-2018	DEF	16/10/2018	Peppers	3
INV001-2019	ABC	9/02/2019	Sprouts	4
INV001-2019	ABC	9/02/2019	Peppers	3
INV002-2019	ABC	18/11/2019	Egg	1
INV002-2019	ABC	18/11/2019	Salad	6
INV003-2019	DEF	12/12/2019	Egg	1

The result of this measure would be: 

ABC : 2 (because in 2018 en 2019 the client ABC had more then 1 invoice)

DEF: 1 (because the client DEF had only in 2018 2 invoices and in 2019 only 1 invoice).

Thanks a lot

[PeterRodet]

It works! Thanks a lot Pat !

[mahoneypat · Accepted solution]

First you need to add a calculated column to your table with this expression.  Replace documents with your actual table name in this and the measure below.

Year = YEAR(Documents[Date])

Then you can use this measure in a table visual with the Clientnr column to get your desired result.  This should also give you the correct total in the visual.

Years with >1 =
VAR vSummary =
    ADDCOLUMNS (
        SUMMARIZE (
            Documents,
            Documents[Clientnr],
            Documents[Year]
        ),
        "cDocs",
            CALCULATE (
                DISTINCTCOUNT ( Documents[Documentnr] )
            )
    )
RETURN
    COUNTROWS (
        FILTER (
            vSummary,
            [cDocs] >= 2
        )
    )

Regards,

Pat