Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
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 )
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 , 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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |