Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Szwadron
Frequent Visitor

Dynamic measure to control customers who did not buy anything for last x days

Hi everyone,

 

I would like to ask you to help me with implementing a solution to show customers who DID not buy anything from the sales table for last x days (x is a parameter based on slicer described as NumberDay).

The scenario:

1) I have a table - SalesTable with thre collumns: Customer, Purchase Y/N, DateOfSales

Customer and DateOfSales are just values from a table, and Purchase Y/N is a measure with DAX code:

 

NoPurchase = 
var currentDate = LASTDATE('Calendar'[Date])
var SelectedPeriod = DATESBETWEEN('Calendar'[Date],DATEADD(currentDate,-NumberDay[Value NumberDays],DAY),currentDate)
var DatesInTheSelectedPeriod = CALCULATE(COUNTROWS('SalesTable'), 'Calendar'[Date] IN SelectedPeriod )
RETURN IF(DatesInTheSelectedPeriod = BLANK(), "Y", "N")

 

 

 

and my dashboard looks like that:

 Szwadron_0-1673622931760.png

 

 

so on the screen with my solution those orders for Customer A who were placed later than 217 days in the past are described as Y, and those which were placed sooner are described as N.

 

Hower if I add to my parameter number 195 which was the last orderfrom that customer in the past everyhing is described as Y which means that no order have been placed sooner than 195 days ago and no I would like to implement a solution:

 

to show me those customers who did not buy anything from today till (TODAY - NumberDay) and return them as a table.

 

Is that achievable? 

 

I would be very grateful for any tips how to achieve that.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Szwadron ,

Remove Dateof Sales from visual or add removefilter (Table[DateofSales]) in meausre

 

having your rolling like


CALCULATE(COUNTROWS('SalesTable'),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-NumberDay[Value NumberDays],DAY))

 

or


CALCULATE(COUNTROWS('SalesTable'),DATESINPERIOD('Date'[Date ],today() ,-NumberDay[Value NumberDays],DAY))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Szwadron ,

Remove Dateof Sales from visual or add removefilter (Table[DateofSales]) in meausre

 

having your rolling like


CALCULATE(COUNTROWS('SalesTable'),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-NumberDay[Value NumberDays],DAY))

 

or


CALCULATE(COUNTROWS('SalesTable'),DATESINPERIOD('Date'[Date ],today() ,-NumberDay[Value NumberDays],DAY))

Thank you very much for the reply but I am afraid I don't get it.. 

Shall I add the code you wrote for RemoveFilter somewhere in my current measure called 'NoPurchase'

or shall I create a new measure and paste your code? If so shall I add any filter to it? 

 

@amitchandak 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.