topic Re: Data outside of date range in DAX Commands and Tips

Data outside of date range

viveknz — Sat, 07 Dec 2024 06:35:22 GMT

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])) )

Re: Data outside of date range

Poojara_D12 — Sat, 07 Dec 2024 09:43:28 GMT

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

Re: Data outside of date range

viveknz — Sat, 07 Dec 2024 17:06:44 GMT

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.

Re: Data outside of date range

powerbiexpert22 — Sun, 08 Dec 2024 00:24:12 GMT

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?

Re: Data outside of date range

Anonymous — Mon, 09 Dec 2024 02:12:12 GMT

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.

Re: Data outside of date range

Kedar_Pande — Mon, 09 Dec 2024 16:16:33 GMT

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