Forum Discussion

ligalbert's avatar
ligalbert
Helper I
4 years ago

Consecutive months

I want to only show the customers ID of the customers who have done an order for 3 consecutive months. Is there a way I can create a measure for that   
Need Help
Show and Tell
amitchandak's avatar
amitchandak
Super User
4 years ago

ligalbert , Try a measure with help from date table

 

measure =
Var _1 = calculate(Countx(Values('Date'[MONTH Year]),CALCULATE(sum(Table[Order Count]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
return
countx(Values(Table[AV CustomerID]) ,calculate( If( _1 =3, [AV CustomerID], blank())))

 


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.