Help with DAX for last 10 years calculation with condition

[Anonymous]

Hello,

I need help with some calculation here.

I have two data tables x and y and both are monthly data for last 4 years. I have created 2 measures: X = SUM(x[Qty]) and Y = SUM(y[Qty]). I am trying to calculate a specific rate and want to consider values of y for last 10 years. Because we don't have historical data for last 10 years, we are just using the existing values and multiplying that by 10. So, I have created a measure called Rate as Rate = DIVIDE(X, (Y*10)). This gives right values for selected years 2019 or 2020, for example. However, when we select a year and any month using slicers, then the calculations are completely off. I am not sure, how to get the right calculation in that case whether selecting all months or just one month from a year. I am assuming we need to write a specific condition in DAX. Please help!

In an ideal scenario, I would like to use a DAX that can add last 10 years values of y, if we have data and if we don't have for some years, it should just use average of existing data for the remaining non-existing years. The values should still be right, whether we select one month of a year or the whole year. Any help to resolve this would be appreciated.

Thanks!

[Anonymous]

Okay, so, I was able to write DAX that is accuraetly taking calculation whether I select a specific month or all months. He is the DAX for last 10 years SUMX(DATESINPERIOD(Dates[Date], LASTDATE(Dates[Date]), -10, YEAR), [Y]). However, because I have data only for last 5 years, my calculation stops as soon as date ends 5 years ago. I would like to use the values of very first year (earliest year) for the remaining non-existant years. So, it will be constant value for first 5 years using teh value from teh 6th year, for example. But I want it to be dynamic so that I am able to get 2 more years worth data from history, then the calulation should take constant values from the oldest year again. It should be valid even if we select any specific month from teh slicer.

Thanks in advance to refine this DAX further!

[v-kelly-msft]

Hi @Anonymous ，

You could add an if statement to check whether the minimum date is >LASTDATE(Dates[Date]), -10, YEAR),if so,then return the minimum date,else the lastdate.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!