Messure for active customer on historical dates

[eaj]

I have a invoice table: fact_BillingInvoice (ID_Customer, ID_BillingInvoice, InvoiceDate). A customer is considerd active if it has a purchase within the last year from a given date.

I've tried this messure but i get error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Active customers =

var one_year_back = DATEADD(Dim_Date[Date], -1, YEAR)

Return

CALCULATE(DISTINCTCOUNT(Fact_BillingInvoice[ID_Customer]),

Fact_BillingInvoice[InvoiceDate] >= one_year_back,

Fact_BillingInvoice <= TODAY())

[Anonymous · Accepted solution]

Hi @eaj ,

DATEADD returns a table containing a column of date values. Here you need to return a scalar value.
Please try this:

Active customers =
VAR one_year_back =
    EDATE ( Dim_Date[Date], -12 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Fact_BillingInvoice[ID_Customer] ),
        FILTER (
            ALL ( Fact_BillingInvoice ),
            Fact_BillingInvoice[InvoiceDate] >= one_year_back
                && Fact_BillingInvoice[InvoiceDate] <= TODAY ()
        )
    )

 Or(a measure):

Active customers =
VAR one_year_back =
    EDATE ( MAX(Dim_Date[Date]), -12 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Fact_BillingInvoice[ID_Customer] ),
        Fact_BillingInvoice[InvoiceDate] >= one_year_back,
        Fact_BillingInvoice[InvoiceDate] <= TODAY ()
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[nirali_arora]

You can try the following dax measure

Active Customers =

VAR OneYearBack = DATEADD(Dim_Date[Date], -1, YEAR)

RETURN

CALCULATE( DISTINCTCOUNT(Fact_BillingInvoice[ID_Customer]),

FILTER( Fact_BillingInvoice,

Fact_BillingInvoice[InvoiceDate] >= OneYearBack &&

Fact_BillingInvoice[InvoiceDate] <= TODAY()

)

)

[eaj]

Hi, Thanks for your help but this does not seem to work. Error: a table of multiple values was supplied where a single value was expected