The Moving average in the course uses the
AVERAGEX ( DATESINPERIOD (Dates(Date), LASTDATE ( Dates[Date] ), -1, MONTH ), [Sales LQ]
What would the formula be to calculate this based on a fiscal period number in a non std calendar
Going on your other question, I truly believe you need to setup a normal calendar and then add the appropriate financial years and periods to it.
You will really find it difficult to complete any time intelligence in Power BI without one.
Here’s are some videos to review
Now we have established I can’t use the inbuilt time intelligence ……
The Moving average in the course uses the
AVERAGEX ( DATESINPERIOD (Dates(Date), LASTDATE ( Dates[Date] ), -1, MONTH ), [Sales LQ]
What would the formula be to calculate this based on a fiscal period number in a non std calendar
The important thing for this is how do you want to showcase this.
For example on the below
For everyday here the result is looking back one month (to the same day of the previous month), counting those days between and then running the average of all of the results on between.
This to me would be sufficient based on the model you have. What would be different based on the non standard. Yes the fiscal period are different but how would that effect any one result within a month for example?
I guess what I’m trying to understand is how would your results differ to what are calculated above with another formula? Where would the differences be, and would they be that different?
Where this seem to need further explanation is say for example we are on the 28th of April or the 29th of April, how many days behind do you need for your ‘one period’? It’s not entirely clear to me on each day as you work your way through it what is the appropriate days to be in the calculation at each point.
I would personally feel the normal moving average would be sufficient here, but happy to look into it more with more details.
If you could explain where an individual result would be different and the logic behind it then that will give me more to go on, to explore a solution further.
Thanks
I guess I’m really asking what would the pattern be for replacing Datesinperiod in a non standard calendar
Well understanding what the logic is that you need will answer this within the formula.
As I described above the logic here is important because that will totally determine what goes inside the formula.
I am still not sure what the difference is that you are seeking with this? Are you able to explain the logic you are looking for as per my response above?
Sam