I need to compare count of number of order for previous 3 months

[Anonymous]

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 .  

# of Orders MTD = TOTALMTD([# of Orders ], 'Calendar'[Date])

# of orders MTD (Day)-1 = CALCULATE( [# of Orders MTD],DATEADD(FILTER(DATESMTD('Calendar'[Date]), 'Calendar'[Date]<TODAY()), -1,MONTH))

# of orders MTD (Day)-2 = CALCULATE( [# of Orders MTD],DATEADD(FILTER(DATESMTD('Calendar'[Date]), 'Calendar'[Date]<TODAY()), -2,MONTH))

# of orders MTD (Day)-3 = CALCULATE( [# of Orders MTD],DATEADD(FILTER(DATESMTD('Calendar'[Date]), 'Calendar'[Date]<TODAY()), -3,MONTH))

#  of customer = sumx(summarize(calculatetable(Report_Details)),

Report_Details[Customer Code], "_active",if([# of Orders(Day) MTD]>=[# of orders MTD (Day)-1],if([# of Orders(Day) MTD]>= [# of orders MTD (Day) -2],if([# of Orders(Day) MTD]>=[# of orders MTD (Day) -3],DISTINCTCOUNT(Report_Details[Customer Code]),0),0),0)),[_active])

Thanks,

Ali Mostafa

[Anonymous]

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

# of Orders (DAY) = sumx(summarize(calculatetable(sales_details,FILTER('sales_details',[Total Sales (DAY)]>0)),

sales_details[Customer Code ], "SalesOfDay",distinctcount('Calendar'[Date])),[SalesOfDay])

Then I tried to apply below script to get active customer 

=countx(values([Customer Code]), if([MTD] > [MTD -1] && [MTD] >[MTD2] && [MTD] > [MTD3], [Customer Code], blank()))

Thanks,

Ali 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

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  :

COUNTX(values([Customer Code]), if([ MTD]>= [MTD-1] ,"Active",0))

it shows wong   value when I used belwo script  :

COUNTX(values([Customer Code]), if([ MTD]>= [MTD-1] && [ MTD]>= [MTD-2]  ,"Active",0))

Thanks in advance,

[amitchandak]

@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.

[Anonymous]

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

#  of customer = sumx(summarize(calculatetable(Report_Details)),

Report_Details[Customer Code], "_active",if([# of Orders(Day) MTD]>=[# of orders MTD (Day)-1],if([# of Orders(Day) MTD]>= [# of orders MTD (Day) -2],if([# of Orders(Day) MTD]>=[# of orders MTD (Day) -3],DISTINCTCOUNT(Report_Details[Customer Code]),0),0),0)),[_active])

Thanks,

[amitchandak]

@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()))