Data outside of date range

[viveknz]

I have a customer table that contains customer details along with their last purchase date. I have an area table that is related to customer table by area code. Finally, I have a DateDim table for the dates. I am using a date slicer with relative date as slicer settings. I want to identify customers who haven't purchased within 1 calendar week, 1 month, etc in an area.

I have created a measure to identify the dates outside of the selected value in the date slicer. However it is very slow in performance. Can someone please suggest a better and more efficient way?

Non trading = 
CALCULATE(
    IF(
        MAX(Customer[Last purchase date]) <> SELECTEDVALUE(DateDim[Date]),
        BLANK(), [Last Purchase Date]),
    FILTER(
        ALL(DateDim),
        DateDim[Date] < MIN(DateDim[Date]) || DateDim[Date] > MAX(DateDim[Date]))
)

[Kedar_Pande]

@viveknz 

Optimized Measure:

NonTradingCustomers =
VAR MinDate = MIN(DateDim[Date])
VAR MaxDate = MAX(DateDim[Date])
RETURN
CALCULATE(
COUNTROWS(Customer),
FILTER(
Customer,
Customer[Last purchase date] < MinDate || Customer[Last purchase date] > MaxDate
)
)

Add the NonTradingCustomers measure to a card visual to show the total count of non-trading customers for the selected period.
Use Customer[AreaCode] in a table or matrix visual if you need to group the non-trading customers by area.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[powerbiexpert22]

Hi @viveknz ,

is you dax formula giving correct results and the only issue is performance?

are you using import or direct query connection?

what is the size (in MB or GB) of dataset or Power BI file?

is your database perfornace slow?

are you following star schema design in your data model?

can you please share some sample data?

[Poojara_D12]

Hi @viveknz 

Can you please try this DAX:

NonTradingCustomers =
CALCULATE(
    COUNTROWS(Customer),
    FILTER(
        Customer,
        Customer[Last purchase date] < MIN(DateDim[Date]) -- Last purchase is before the slicer range
            || Customer[Last purchase date] > MAX(DateDim[Date]) -- Last purchase is after the slicer range
    )
)

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[viveknz]

Thanks Poojara. How would this enable me to list the name of the customers who haven't purchased outside of the selected date range? I want to list the customer name with their last purchase date.

[Anonymous · Accepted solution]

Hi @viveknz 

Please try this:

Here I create a set of sample:

Customer:

DateDim:

Make sure that there aren't any relationships between the 2 tables:

Then create a measure:

MEASURE =
VAR _Slicer =
    ALLSELECTED ( DateDim[Date] )
RETURN
    IF ( NOT MAX ( 'Customer'[Last purchase date] ) IN _Slicer, 1 )

Add a table visual and drag the measure to the 'Filters on this visual' in the Filters pane, then filter the value of the measure is 1 and click the Apply filter:

The result is as follow:

Best Regards

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