No Orders last Month

[Anonymous]

Hi all,

I am trying to build a report that shows customers who did not order within the last month.  They want to see the customers who haven't ordered anything from the dates of Nov 29 2021 to Dec 27 2021 of active customers who did not order anything. Any help would be great!  Thanks! 

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can try this

Lost Customers = 
VAR _CurrentDate = MAX('Date'[Date])
VAR _1MonthsAgo = EOMONTH(_CurrentDate, -1)
VAR _Last1MonthCustomers =
CALCULATETABLE(
    VALUES(Sales[Customer_ID]),
    DATESINPERIOD('Date'[Date], _CurrentDate, -1, MONTH)
)
VAR _BeforeLast1MonthCustomers = 
CALCULATETABLE(
    VALUES(Sales[Customer_ID]),
    REMOVEFILTERS('Date'[Date]),
    'Date'[Date] <= _1MonthsAgo
)
VAR _LostCustomers = EXCEPT(_BeforeLast1MonthCustomers, _Last1MonthCustomers)
RETURN
    COUNTROWS(_LostCustomers)

You can have a look at this video.

https://www.youtube.com/watch?v=m1uupRDWzQw

https://radacad.com/lost-customers-dax-calculation-for-power-bi

Regards,

Harsh Nathani