Help with DAX to apply rates specific to Year

[Anonymous]

Hello!

I have a Rates table which shows Rates specific to 1st Year, 2nd Year., & 3rd Year. 

In this example, we are looking at Rates for last 3 years and am looking to use a measure for Sales w/ Rates. When we select a product and any year/month from the slicer, that particular year would be considered as 1st Year/Month and the corresponding Rate needs to be applied to Sales for that Year/Month. For example, selected Year is 2022, then the rate for 1st Year should be considered for 2022 (Sales w/ Rates = Sales * 0.90), for 2021, 2nd Year Rates need to be considered for 2021 (Sales *.80) & for 2020, 3rd Year Rates need to be considered (Sales * .70)

Link to file: https://drive.google.com/file/d/1LhxX98ZTTrWcAq7LhVn_YT0tGguanqpp/view?usp=sharing

In the example Table, I am looking to add Sales w/ Rates next to Sales. 

I am not sure how to perform this. Any help with DAX would be appreciated.

Thanks!

[Anonymous]

How do we go back to 5 years or 7 years in this example? What changes would we need?

Thanks again for your help!

[sturlaws]

Hi,

first add an index column on your rates-table, where 1st year = 0, 2nd year = 1 and 3rd year=2

Then create this measure:

sales with rates =
VAR _currentYear =
    YEAR ( TODAY () )
VAR _selectectYear =
    YEAR ( CALCULATE ( SELECTEDVALUE ( Table137[Date] ) ) )
VAR _rate =
    LOOKUPVALUE ( Rates[Rates], Rates[index], _currentYear - _selectectYear )
RETURN
    SUM ( Table137[Sales] ) * _rate

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Hi @sturlaws ,

Thank you so much. It does work to the most part. It seems to work for selected month and year, but it shows Blank values, when I select a Year and all months. I have a slicer for Year and a slicer for Month in my original data.

I just created a Date table for this sample data hoping this could be an issue. Attached is the link with Date table added and as can be seen, when we select all months and any year, it gives blank. Otherwise, when we select a month & year, it does give correct values.

Link for pbx file: https://drive.google.com/file/d/1TZ7CJwyuBeQchryTNxkM_27VFbW0IhRX/view?usp=sharing

Thanks for your help again!

[v-zhangti]

Hi, @Anonymous 

Please try the following methods.

sales with rates =
VAR _currentYear =
    YEAR ( TODAY () )
VAR _selectectYear =
    SELECTEDVALUE ( Dates[Year] )
VAR _rate =
    LOOKUPVALUE ( Rates[Rates], Rates[Index], _currentYear - _selectectYear )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Month] ) <> BLANK (),
        SUM ( Table137[Sales] ) * _rate,
        CALCULATE ( SUM ( Table137[Sales] ), ALLSELECTED ( Table137[Country] ) ) * _rate
    )

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much @v-zhangti !

Yes, it gives the right answer for year also now. I didn't notice this earlier, but I was also looking to see the values of previous 3 years for selected year/month.  So, for 2022, values of 2022, 2021 & 2020 while using corresponding rates.

Towards the end, I would like to add the values of these 3 years. In this case, I don't have values for 2020 and in such case I would like to have data same as the very first years data. So, 2020 values will be same as 2021 as we don't have values for 2020.  Would adding these up be possible? 

Thanks again for helping out!

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

previous 3 years = 
VAR _currentYear =
    YEAR ( TODAY () )
VAR _selectectYear =
    SELECTEDVALUE ( Dates[Year] )
VAR _rate =
    LOOKUPVALUE ( Rates[Rates], Rates[Index], _currentYear - _selectectYear )
VAR _prerate=LOOKUPVALUE ( Rates[Rates], Rates[Index], _currentYear - _selectectYear+1 )

RETURN
    IF (
        SELECTEDVALUE ( Dates[Year] ) <> BLANK (),
        SUM ( Table137[Sales] ) * _rate+CALCULATE(SUM(Table137[Sales]),FILTER(ALL(Dates),[Year]=_selectectYear-1))*_prerate*2,
        CALCULATE ( SUM ( Table137[Sales] ), ALLSELECTED ( Table137[Country] ) ) 
    )

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks again @v-zhangti !

This seems to be very close to the right value for 2022  which should be 4148. The value of 711 for 2022 is right, if we don't use last 3 years. But we need to use all past 3 years with rates. So, I am expecting the follwing output when we select 2022 as Year:

When we choose 2022 in this example, rates for previous 3 years should be applied and then all 3 years should be added. Because we don't have value for 2020 in this case, we keep the value of first available year in the data and thus, we use same value of 2021 for 2020 as in above table. Same applies if any specific month for any specific year is chosen. 

Thanks again for your help!

[v-zhangti · Accepted solution]

Hi, @Anonymous 

Please try the following formula.

previous 3 years = 
VAR _currentYear =
    YEAR ( TODAY () )
VAR _selectectYear =
    SELECTEDVALUE ( Dates[Year] )
VAR _rate =
    LOOKUPVALUE ( Rates[Rates], Rates[Index], _currentYear - _selectectYear )
VAR _prerate=LOOKUPVALUE ( Rates[Rates], Rates[Index], _currentYear - _selectectYear+1 )
VAR _preprerate=LOOKUPVALUE ( Rates[Rates], Rates[Index], _currentYear - _selectectYear+2 )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Year] ) <> BLANK (),
        SUM ( Table137[Sales] ) * _rate+CALCULATE(SUM(Table137[Sales]),FILTER(ALL(Dates),[Year]=_selectectYear-1))*_prerate
        +CALCULATE(SUM(Table137[Sales]),FILTER(ALL(Dates),[Year]=_selectectYear-1))*_preprerate,
        CALCULATE ( SUM ( Table137[Sales] ), ALLSELECTED ( Table137[Country] ) ) 
    )

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This works great! Thank you so much for all the help @v-zhangti !

And if I needed to see the results for last 5 years instead, I will just need to change the following in your formula, right?

VAR _preprerate=LOOKUPVALUE ( Rates[Rates], Rates[Index], _currentYear - _selectectYear+4 )

Is there any other change I would need to perform? Also, which lines helps with filling gthe missing values of previous years such as for 2020 in this case?

It will be very helpful to understand your logic. Thanks a bunch for solving this puzzle!