dynamic rolling average based on month slicer

[selpaqm]

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,

Sample.pbix

[selpaqm · Accepted solution]

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

Solution.pbix 

[amitchandak]

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

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 ?

[selpaqm]

@amitchandak yes coming from date table.

also below you may find a sample pbix.

New sample.pbix 

[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,

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