How to calculate active customers current and previous month by customer code

[ommfin123]

Good day.

ROA=running of account

I wanted to get the previous month active customer by using:

Previous Month ROA = CALCULATE (DISTINCTCOUNT('Running of Account'[Debtor Code]), PREVIOUSMONTH('Running of Account'[Date]))

But i got no value.

For current month ROA I'm using:

Current Month ROA =

CALCULATE ( DISTINCTCOUNT('Running of Account'[Debtor Code]),

    YEAR( 'Calendar'[Date]) = YEAR(MAX('Calendar'[Year Month])),

    MONTH( 'Calendar'[Date]) = MONTH(MAX('Calendar'[Year Month])))

And for current month is fine.

May I know where I have do it wrong?

Below are my example data.

Calendar table

Running of account data:

Thank you so much.

[some_bih · Accepted solution]

Hi @ommfin123 in general CALCULATE could accept many filter arguments.

In your part Name],), this red comma is extra?

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

[ommfin123]

Dear @some_bih ,

The red comma removed. But it's said my syntax for PREVIOUSMONTH is incorrect.

Very greatful for your kindness.

[some_bih]

Hi @ommfin123 if it was ok return it, sorry.

[some_bih]

Hi @ommfin123  DAX function PREVIOUSMONTH is one of the time intelligence functions in DAX, which works the best when applied on Date / Calendar table.

Try to change your part PREVIOUSMONTH('Running of Account'[Date])) to your Date table accordingly and check results. Hope this help

[ommfin123]

Hi @some_bih ! Yeah! It's works! But I have a question too, I have repeated customers names with different customers' code, how can I add AND function to filter 2 conditions in this case? 

Previous Month ROA = CALCULATE(

FILTER(

    DISTINCTCOUNT('Running of Account'[Debtor Code],

    DISTINCTCOUNT('Running of Account'[Debtor Name],)

    PREVIOUSMONTH('Calendar'[Date])))