count number of customer 3 month ago

[daedah]

Exceuse me please

I 'm trying the dax formula for district count number of customer 3 month ago,

I used formula

numberofccust3monthago  =

var customersCurrentMonth = VALUES('Sale_2017-2019'[CUST_ID])

var dates1MonthAgo = PREVIOUSMONTH('Sale_2017-2019'[BILL_DATE])

var dates2MonthsAgo = PREVIOUSMONTH(dates1MonthAgo)

var dates3Monthsago = PREVIOUSMONTH(dates2MonthsAgo)

var datesPast3months = UNION(dates1MonthAgo, dates2MonthsAgo,dates3Monthsago)

var customersPast3months = CALCULATETABLE(

VALUES('Sale_2017-2019'[CUST_ID])

, ALL('Sale_2017-2019'[BILL_DATE].[Month],'Sale_2017-2019'[BILL_DATE].[MonthNo],'Sale_2017-2019'[BILL_DATE].[Year])

,'Sale_2017-2019'[BILL_DATE] IN datesPast3months)

RETURN SUMX(customersCurrentMonth, IF(

[CUST_ID] IN customersPast3months

,1,0))

But it mistake

As following below picture

Thank you for your helphttps://drive.google.com/file/d/1UzeeB14hWkPPSdXfqVIQJbosFekPkq78/view?usp=sharing 

[Anonymous]

Hi @daedah,

Edate function might be helpful for you.

Please check following measure and see if the result achieve your expectation:

sum_last3month =

VAR l3 =

    EDATE ( MAX ( 'Table'[Date] ), -3 )

VAR d =

    MAX ( 'Table'[Date] )

RETURN

    CALCULATE (

        SUM ( 'Table'[Customer] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Date] < d && 'Table'[Date] >= l3 )

)

Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[daedah]

excuse me please

It formula don't show value

Recommend again please

[Anonymous]

Hi @daedah ,

As we can see in the picture, the "customer" column in my sample data actually is "number of customers".

If the "customer" column in your data is "name of customers", please replace "SUM ( 'Table'[Customer] )" to "DISTINCTCOUNT('Table'[Customer])"

Best Regards,
Jay

[Ashish_Mathur]

Hi,

Replace SUM ( 'Table'[Customer] ) with DISTINCTCOUNT('Table'[Customer])

Hope this helps.