Actually this was perfect until I realized that my dataset of [Dates] skips weekends and only capture buisness days. So I don'y really have a full continuityit by dates and basically every 5 days it skips to the next week starting date. This also apears in holidays where I have no date because it is not a business day.
I like the way you approached it but the (-9) thing probably won't work in this case.
Any other option?
By the way, I used your first option as a column (2.1)
(The [Average Last 10 Business Days] is a calculation I did manually in my Excel sheet)
1. Nope. there are no records for non-business days so basically the data is sequential (just skipping days where data=[blank]).
2. you are right my mockup is bad but you got the idea...
One other thing that I realized just recently is the fact that when I'm using DirectQuery (because I need real-time data) PowerBI limits me in implementing any data query and I have to do it in the database. Not sure why PowerBI has this limitation.