Latest year & Previous Latest Year - Filtered by Report Year & Quarter.

[matrix_user]

I am trying to display in a clustered column chart visual the current/latest year data as well as previous latest year data but govern by a "Report" year & quarter.

If I select Report year "2022" and "Qrt 4", I want it to filter to the current and previous data based on Report years. For example, my results on my visual would be " 1" (with 2011 being the latest year data on record) and "3" (with 2010 being the latest year data on record)

If I select Report year "2021" and "Qrt 4", my results on my visual would be " 3" (with 2010 being the latest year data on record) and "2" (with 2011 being the latest year data on record).

I am currently using the following measure:

Latest year & previous latest year = VAR ReferenceDate = MAX ('Date'[Date])

VAR PreviousDates = DATESINPERIOD (

        'Previous Date'[Date], ReferenceDate, -2, YEAR )

VAR Result = CALCULATE ( [Total Confirmed],

        REMOVEFILTERS ( 'Date' ),

        KEEPFILTERS ( PreviousDates ),

        USERELATIONSHIP ('Date'[Date],'Previous Date'[Date]))

RETURN Result

If anyone could help - that would be appreciate.

https://drive.google.com/drive/folders/1OtvkxXd8mnAXSSGGwW0xi8yvb_NJRbf1?usp=sharing

https://www.dropbox.com/scl/fo/fsuaprxpodazygfe6excd/h?dl=0&rlkey=2wkn4jgx3fqtcd3pjmiqki7g0

Thank you.

[matrix_user]

Thank you Ibendlin,

I have included calculated columns. I am still lacking the ability to pull data from previous report for sameperiod.

[lbendlin]

If your fact data doesn't have date level granularity then you can designate a substitute date, for example the first day of the year, or the first day of the quarter.  That will allow you to properly link to the Calendar table, and to use the built-in time intelligence functions.