Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 6
v-stephen-msft
Community Support
Community Support

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.

 

 

Anonymous
Not applicable

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

Hi @Anonymous ,

 

First create a what-if parameter.

1.png

Then create the measure like

Average =
CALCULATE (
    AVERAGE ( 'Table'[value] ),
    FILTER (
        'Table 2',
        [Date] <= DATE ( YEAR ( TODAY () ), 3, 31 )
            && [Date]
                >= DATE ( YEAR ( TODAY () ) - [Parameter Value] + 1, 4, 1 )
    )
)

2.png

 

 

 

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.

 

Anonymous
Not applicable

Hi Stephen,

 

Thanks For your quick response.

 

But it shows average for only current regulatory year.

 

Regards,

Shivam

Hi @Anonymous ,

 

Please provide some dummy data and 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.

 

Anonymous
Not applicable

Hi Stephen,

 

Can I get any response please.

 

Regards,

Shivam Kumar

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors