Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to Solution.
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))
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |