Forum Discussion
Helper Vdynamic rolling average based on month slicer
I need to add dynamic rolling average, already looked for a while in this forum and could not get really what i need.
it should be changed when i changed the month slicer which is added like 1,2,3,4,5,6,7,8,9,10,11,12
it is already created inside date table which is created with auto calender command and relationship is created. I have just shared a sample view of my table. in this example when 2 is selected in month slicer 1 month average for customer a should be 0% because nothing ordered by a on February (on ordered/ or not column, 0 is not ordered, 1 is ordered) . for 3 months rolling (means feb, jan and Dec19) customer a's average is 25% and for b is 67%.
so if I changed to month slicer to 12 3 months should calculates Oct'19, Nov'19 and Dec'19 and results should be for Customer a is 75% and customer b is 75%.
i can calculate for last month figures but when i add slicer it would not worked
please click for sample pbix.
Regards,
amitchandak thanks your foot steps helped me but actually I have solved it as below.
3%R = DIVIDE( CALCULATE(SUM('Order'[Order/ not ordered]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH)), CALCULATE(COUNT('Order'[Order/ not ordered]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH)))you also find entire as solution pbix on below link.
8 Replies
- selpaqm
amitchandak thanks your foot steps helped me but actually I have solved it as below.
3%R = DIVIDE( CALCULATE(SUM('Order'[Order/ not ordered]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH)), CALCULATE(COUNT('Order'[Order/ not ordered]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH)))you also find entire as solution pbix on below link.
- amitchandak
Super User
selpaqm , did not get it completely. But with a count slicer you can have rolling like
Rolling = var _max = max(Slicer[Value]) return CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),_max,MONTH)) Rolling = var _max = max(Slicer[Value]) return Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),_max,MONTH))- selpaqm
amitchandak thank you also for your comment but it is not worked for this one. can you please check the link and advise?
Regards,
- selpaqm
hi amitchandak
i am stuck on another formula. that is completely related with that one. my table also has salesman column. and now I want to create a table to check how many order received by salesman based on customer.
3%R = DIVIDE( CALCULATE(SUM('Order'[Order/ not ordered]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH)), CALCULATE(COUNT('Order'[Order/ not ordered]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-3,MONTH)))I have created another measure. that is
John= calculate(order[3%R],filter(order,order[salesman]="John"))so if I do not choose any month from month slicer, table shows 3 month numbers and averages correct. however, when i choose a month from slicer, due to filter just number turns 1 month figures. how I amend this?
- amitchandak
selpaqm , Hope that month is coming from date table ?
- TomMartens
Hey selpaqm ,
please create a pbix that contains sample data but still reflects your data model, meaning including the calendar table you created.
Basically, the business logic is not clear. If the monthnumber 12 is selected from the slicer how will the months be determined?
It could be 2020-12, 2020-11, 2020-10 or as mentioned 2019-12, 2019-11, 2029-10.
Please describe the logic to determine the 3 months in more detail.
Regards,
Tom
- selpaqm
TomMartens thanks for your quick response. I have added detailed sample. can you please check and advise.
