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.

Top Solution Authors