Looking to calculate a 3 month rolling based on multiple filters

[bmturney]

I have a set of data that looks something like this...

Location	Date	VendorName	UnitsSold	TotalUnits	VendorShare
1	1/1/2021	VendorA	200	1000	20%
2	1/1/2021	VendorB	300	1000	30%
3	1/1/2021	VendorC	500	1000	50%
1	2/1/2021	VendorA	250	900	27.78%
2	2/1/2021	VendorB	300	900	33.33%
3	2/1/2021	VendorC	450	900	50%
1	3/1/2021	VendorA	300	1200	25%
2	3/1/2021	VendorB	600	1200	50%
3	3/1/2021	VendorC	300	1200	25%
1	4/1/2021	VendorA	400	1000	40%

What I want to be able to do... is by location and by vendor, calculate a 3 month rolling average for the VendorShare.... so that if I'm looking at Location 1 > Vendor A>January the 3 month rolling average for Vendor Share would be 20%... but for February it would be 23.89%(AVERAGE(20%, 27.78%)) .. for March it would be 24.26% (AVERAGE(20%, 27.78%, 25%))... for April it would be 30.93% (AVERAGE(27.78%, 25%, 40%))... etc... 

if I PIVOT on VendorName I'm able to make it work by creating a measure using the AverageX function with a DATESINPERIOD filter... however, I would like to do this without having to do the PIVOT... because I would like to create a slicer that allows me to easily filter on VendorName... 

Any thoughts/suggestions would be greatly appreciated?

[Krutigawale33 · Accepted solution]

Hello @bmturney ,

try measure

rolling = CALCULATE(AVERAGE(Vendor[VendorShare]),ALLEXCEPT(Vendor,Vendor[VendorName],Vendor[Location]),

DATESINPERIOD(Vendor[Date],

LASTDATE(Vendor[Date]),-3, MONTH

))

[bmturney]

Awesome... this worked perfectly for what I needed... if we were in the same room I would put my arm around your shoulders and rub my knuckles on top of your head (act of endearment here in the U.S.  ;^) )

[Krutigawale33]

Thanks @bmturney I am happy that solves your issue.

[amitchandak]

@bmturney , Try a measure like with help from date table

Rolling 3 = divide(CALCULATE(sum(Table[Unit Sold]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
CALCULATE(sum(Table[Unit Sold]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH), filter(allselected(Table),Table[Location] = max(Table[Location]))))

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.