cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Help with DAX to apply rates specific to Year

Hello!

 

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

kamsingh11_0-1654276610365.png

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!

1 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] ) ) 
    )

vzhangti_0-1654766797898.png

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.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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
Super User
Super User

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
Not applicable

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!

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
    )

vzhangti_0-1654595063113.png

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
Not applicable

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!

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] ) ) 
    )

vzhangti_0-1654669729516.png

 

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
Not applicable

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:

kamsingh11_1-1654716319060.png

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!

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] ) ) 
    )

vzhangti_0-1654766797898.png

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
Not applicable

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!

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.