Forum Discussion
Power Bi Period Average For 3 and 5 year
Hi,
In my report we have one slicer named Average Period which has value 3 and 5, and a Calendar Date Table. and regulaory year for my report starts from 01-apr to 31Mar i.e 01-Apr-2020 to 31-Mar-2021
So If I select 3 as average period it should show average of sales for last 3 year excluding current regulatory year i.e it should calculate average for regulatory year 2021,2020,2019 and it shoud exclude current regulatory year i.e. 2021.
same for Average period 5, it should calculate average for last 5 regulatory year excluding current regulatory year.
Also It should be static it should not interact with date filter.
Can I get help on this. Your help will be much appreciated on this.
Regards,
Shivam Kumar
6 Replies
Hi Anonymous ,
Your date column is from 01-Apr-2020 to 31-Mar-2021. What's the point of filtering 2019 when you select '3' in the slicer?
Another question. When selecting '3', what does it mean to calculate the average sales of 2019, 2020, and 2021 but exclude 2021? Do you only calculate the average value of 2019, 2020?
If possible, please provide some sample data and give the corresponding expected results.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen,
Thanks For jumpng on this.
No I have calendar from 2011 onwards, but it's the regulatory year which starts from 01Apr and end on 31Mar of every year."01-Apr-2020 to 31-Mar-2021" I have just given for reference.
FYI, I am calculating average here based on regulatory year, Like let's say we are now in regulatory year 2022, So when I select Average Period as 3, it should return average for last 3 regulatory year excluding current regulatory year i.e.. it should calcualte average for regulatory year 2021, 2020 and 2019 and it should exclude current regulatory year i.e. 2022.
Same is the case for Average period 5 it should exclude regulatory year 2022 and calucalte average for last 5 year i.e. 2021, 2020,2019,2018,2017.
Regards,
Shivam Kumar