How to display a measure of same period for last serveral years when a specific date is filtered?

[Anonymous]

I have a problem and It is very difficult for me.

So, I ask for help.

I have to display a measure of same period for last several years when a specific date is filtered.

I have tables below:

FactReal

Date	Real
2017-01-01	15
2017-01-02	39
2017-01-03	5
2018-01-01	36
2018-01-02	10
2018-01-03	35
2018-01-04	26
2019-01-01	50
2019-01-02	28
2019-01-03	49

FactTarget

Date	Target
2017-01-01	70
2017-02-01	71
2018-01-01	120
2018-02-01	98
2019-01-01	140
2019-02-01	147

DimDate

Date	Year
2017-01-01	Y2017
2017-01-02	Y2017
2017-01-03	Y2017
2017-01-04	Y2017
2017-01-05	Y2017
2017-01-06	Y2017
2017-01-07	Y2017
2017-01-08	Y2017
2017-01-09	Y2017
…	…
2019-12-28	Y2019
2019-12-29	Y2019
2019-12-30	Y2019
2019-12-31	Y2019

And I have measures:

RealYTD = CALCULATE(SUM('FactReal'[Real]), DATESYTD('DimDate'[Date])
TargetYTD = CALCULATE(SUM('FactTarget'[Target]), DATESYTD('DimDate'[Date])

Progress Rate = DIVIDE([RealYTD]), [TargetYTD]))

I want to display [Progress Rate] of YTD for last 3 years when a specific date of DimDate is filtered.

For example,
Visualization have to display [Progress Rate] of YTD for 2017, [Progress Rate] of YTD for 2018, [Progress Rate] of YTD for 2019 when I choose "2019-01-02" 

I think there should be a table like this:

FactAggregation

Date	SubDate	RealYTD	TargetYTD	Progress Rate
2019-01-01	2017-01-01	15	70	21.43%
2019-01-01	2018-01-01	36	120	30.00%
2019-01-01	2019-01-01	50	140	35.71%
2019-01-02	2017-01-02	54	70	77.14%
2019-01-02	2018-01-02	46	120	38.33%
2019-01-02	2019-01-02	78	140	55.71%
2019-01-03	2017-01-03	59	70	84.29%
2019-01-03	2018-01-03	81	120	67.50%
2019-01-03	2019-01-03	127	140	90.71%

I think have to have a relationship between FactAggregation[Date] and DimDate[Date]
And then give a FactAggregation[SubDate] column to x-axis of visualization.

And give a FactAggregation[Progress Rate] column to value of visualization.

The date slicer must be a date column in DimDate because other visualizations are filtered by this slicer.

I think it, but I don't know how to create a table like FactAggregation
So, I am suffering from this problem.

Please help me.

P.S. If you have a other better solution, please suggest for me 

Thank you

[Anonymous]

Hi, 

You have two ways to do this

1. Using calculation Groups:

You could do this using the below mentioned link 

https://www.sqlbi.com/articles/introducing-calculation-groups/

2. Using SELECTEDVALUE DAX function

Progress Rate_2017= VAR CURRENT_YEAR-2= YEAR(SELECTEDVALUE(DATE COLUMN))-2

Var current_date= SELECTEDVALUE(DATE COLUMN FILTER)

RETURN CALCULATE(SUMX(FILTER(TABLE, YEAR=CURRENT_YEAR-2),REAL),DATESINPERIOD(DATES,CURRENT_DATES,-2,YEAR)/CALCULATE(SUMX(FILTER(TABLE, YEAR=CURRENT_YEAR-2),TARGET),DATESINPERIOD(DATES,CURRENT_DATES,-2,YEAR))

REPEAT THE SAME FOR 2018 (CURRENT YEAR-1). AND FOR CURRENT YEAR (2019) JUST LEAVE IT AS SELECTEDVALUE IN THE VAR STATEMENT 

DO ACCEPT AS SOLUTION IF IT SOLVES YOUR PROBLEM

[kentyler]

Consider adding a column for the 2 other years you are interested in to the dimDate table. Then when someone selects a year, the previous year and the year before that will be available to your Dax code directly in the dimDate table. That should make it much easier to do the calculations against them.