Count Cus. , within 1 month , 3 months rolling , YTD ( Based on Month and Product Code )

[Chanleakna123]

Hi All ,

I'm seeking the right calculation when i filter with Month the first table is turning Wrong ,

seems not follow the Month Interaction. 

I have  3 tables : month , master customer , Actual Customer purchase with product code

I have used measure such below : 

1 month = DISTINCTCOUNT('Table'[Customer Code])

Previous 3 month = SUMX(DATESINPERIOD('Table'[Delivery Date],MAXX(ALL('Table'),[Delivery Date]),-3,MONTH),[1 month])

YTD :  1 year = SUMX(DATESINPERIOD('Table'[Delivery Date],MAXX(ALL('Table'),[Delivery Date]),-1,YEAR),[1 month])

the Result should be : in September based on Filter :

1 month = 1 customer Purchased 

3 months rolling ( Jul , Aug , Sep ) = Result : 3 customers purchased product 3406 , 2 customers purchased product 2737
YTD = 4 customers purchased product 3406 , 3 customers purchased product 2737 

Please note that : we don't care how many transaction of customers purchase our products , within 1 month if they purchase 10 times , we still count them 1 based on Products code and Month. 

Next month if the same customer purchase agian  we count them 1 again  . so we count 2 ( same customer ) 

[v-jingzhang]

Hi @Chanleakna123 

If I understand it correctly, you could try below measures for the rolling 3 months and YTD.

Previous 3 month = COUNTROWS(CALCULATETABLE(SUMMARIZE('Table','Table'[Customer Code],'Date'[YearMonth]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH)))

1 year = COUNTROWS(CALCULATETABLE(SUMMARIZE('Table','Table'[Customer Code],'Date'[YearMonth]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-1,YEAR)))

Let me know if you have any questions. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Chanleakna123]

@amitchandak  , can you help me on this ?

[amitchandak]

@Chanleakna123 , you should use date table for that

YTD Sales = CALCULATE([1 month],DATESYTD('Date'[Date],"12/31"))

Rolling 3 = CALCULATE([1 month],DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

refer if needed

Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

[Chanleakna123]

hi @amitchandak , it doesnt'work , show blank , even i put -3 or -12 , still show the same.