Three-year Average based on only Year and Quarter Number

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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