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 All,
I need a help in below DAX "# of customer".
I need to get count of customer that has same or grater than number of order comparing # of order MTD with MTD-1 and MTD-2 and MTD-3 .
Thanks,
Ali Mostafa
Hi @Ashish_Mathur ,
currently , I grouped number of order of the day as one order .
Then I'm counting number of day by month by customer using below script :
Total Sales (DAY) = CALCULATE(sum(sales),day(sales_details[Invoice Date]))
=countx(values([Customer Code]), if([MTD] > [MTD -1] && [MTD] >[MTD2] && [MTD] > [MTD3], [Customer Code], blank()))
Thanks,
Ali
Hi,
Share some data and show the expected result.
Hi @amitchandak ,
I tried the belwo solution, still shows wrong value :
=countx(values([Customer Code]), if([MTD] > [MTD -1] && [MTD] >[MTD2] && [MTD] > [MTD3], [Customer Code], blank()))
I changed the DAX script as below , when I used one condtion in the if statment , it gives correct value , and if I try to add one more condtion using && I got wrong value :
it shows correct value when I used belwo script :
it shows wong value when I used belwo script :
@Anonymous , You need to have selected date date, then with help from date table you can use
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
Without selecting date
MTD Today =
var _min = eomonth(today(),-1)+1
var _day = datediff(_min, today(),day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max && 'Date'[Day of Year] <= _day) )
LYTD Today =
var _min = eomonth(today(),-2)+1
var _max = date(year(today()),month(today())-1,day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max && 'Date'[Day of Year] <= _day))
Previous Month complete =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max))
same way add other months
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi @amitchandak ,
Thanks for your reply , I need help on below meause :
The logic is : if the count of order by customer MTD is equal or greater than count of order by customer MTD-1 AND count of order by customer MTD is equal or greater than count of order by month by customer MTD-2 AND count of order by customer MTD is equal or greater than count of order by customer MTD-3 then count the customer else 0
@Anonymous , something like this
if([MTD] > [MTD -1] && [MTD] >[MTD2] && [MTD] > [MTD3], "Active", "Inactive")
count =
countx(values(Report_Details[Customer Code]), if([MTD] > [MTD -1] && [MTD] >[MTD2] && [MTD] > [MTD3], [Customer Code], blank()))
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |