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

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 Helper V
Helper V

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

Highlighted
amkumar5 Helper V
Helper V

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors