cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amkumar5 Member
Member

Three-year Average based on only Year and Quarter Number

Hi, I all. I need help with this scenario:

All I have is Year and Quarter in my Snapshot table (Snapshot will be taken at end of each Quarter).

So now I want to run Quarterly ,Three-Year Average Reports, and Trend Reports.

I have Year, Quarter, ProductType, Store/Online Slicers.

 

So when user selected 2019.Q1, I need Sum and Average of 2019.Q1, 2018.Q1, and 2017.Q1 Sales.

                              For 2018.Q3, I need Sum and Average of 2018.Q3, 2017.Q3, and 2016.Q3 Sales.

But the problem is as soon as I select a year and Quarter, Power BI automoaticlally filters data.

 

I have Matrix Visual with Product Category, Total Sales, Average.

I still want to apply filters for Product Category, SalesType (Online/Store) if there is any selection.

 

Thank you in advance.

3 REPLIES 3
Super User IV
Super User IV

Re: Three-year Average based on only Year and Quarter Number

Show example data, please, and paste in the model layout. Best would be if you could paste a link to an example file, which is best stored on OneDrive.

Thanks.

Best
D.
amkumar5 Member
Member

Re: Three-year Average based on only Year and Quarter Number

Hi Darek,

 

Thanks for the response. Please find attached link for data spreadsheet and pbix file.

https://1drv.ms/u/s!Ai0GzdHCLgzPkkCWfVNW4XlZ5Rt3?e=7QePj3

 

- Kumar

amkumar5 Member
Member

Re: Three-year Average based on only Year and Quarter Number

I think I found way to start with:

Step 1: First I've created a Date column in SalesFactSnapshot table using existing columns Year and Quarter

      DATE(SNASPHOT_Year,Snapshot_Quarter*3,1)

 

Step 2: Created a DateD_Dim table and created FK relationship to SalesFactSnapshot table.

 

Now I can use all the Power BI built-in Date, time-intelligence functions such as SAMEPERIODLASTYEAR,PREVIOSQUARTER.

 

For 3 years SalesTotal of same Quarter:

Prev3YearFilings = CALCULATE([TotalSales],FILTER(ALL(SalesFactSnapshot [YearS]),SalesFactSnapshot [YearS]>SELECTEDVALUE(SalesFactSnapshot [YearS])-2))
 
Year is a Slicer, I dit not mentioned because  I want same Quarter:
If user selects 2019.Q1, then I need total and Average of 2019.Q1, 2018.Q1, and 2017.Q1
 
Hope this helps.
 
please let me know if there is a better way

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors