DAX selected years

[ngomes]

Good afternoon

I have a report in which the user selects the years in which he wants to compare quantities sold, he can choose the years he wants, for example, select 2022, 2020, 2014

I have the calendar table linked with the database.

I have a dax measure with the following:
CALCULATE(sum(Database[Quantity]), SAMEPERIODLASTYEAR(Calendar[Date]))

this formula returns the values of 2021 not the values of the selected previous year which would be 2020

my question is how can i build a dax measure to return the values of 2020 or 2014 for example

[ngomes · Accepted solution]

Hello

after a lot of struggle i found the solution to my problem

basically I had to create several calendars and use the formula CALCULATE([Year Value],USERELATIONSHIP('Calendar Year 1'[Date],Sales[Date])) for each selected year

it Works very well
send the example for knowledge
EXEMPLE 

Thank you all for your help

[aj1973]

Good trick, However watch out the performance of your report, Date Columns consume space, memory and energy. 

[lbendlin]

this formula returns the values of 2021 not the values of the selected previous year which would be 2020

SAMEPERIODLASTYEAR  means exactly that.  Power BI has no idea that you mean "the maximum selected year that is smaller than the year that is currently in context".  You will have to write your own measure for that, and you will need to decide what to do for the first selected year (2014)  - return BLANK() ?

[ngomes]

good evening
thanks for the answers

in the end I need to compare the values ​​of the years that the user has chosen, that is, compare 2022 with 2020 and 2020 with 2014 (example)

I have to find a way to find out which years the user has chosen and compare them
I appreciate all the help you can give me
thanks

[lbendlin]

Read about using the FILTERS() function.

[ngomes]

thanks ibendlin

I read a lot about filters with dax but I'm still having difficulties comparing the year 2022 with 2020 and 2020 with 2014 for example

I have slicer per year and as I said at the beginning the user can choose any slicer year

[lbendlin]

I didn't say filters, I said "the FILTERS() function". These are slightly different things.  The FILTERS () function allows you to retrieve the currently selected values of a column, and to then create computations in DAX with them.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[aj1973]

Hi @ngomes 

Since you want to compare Quantities by year selected, I think it would be simple to add 3 date       slicers and cards for each selected year using "Edit interaction" feature to stop slicers from interacting with the wrong cards.

This way you leave the liberty for the user to choose what year on each slicer and for the cards to display the quantity of sales in each year selected 

[ngomes]

I have this slicer for the user to choose the years

I don't know if I explained myself well, but what I want are not the values of the chosen years, these I have already calculated
what intending is to compare the chosen years

[ngomes]

Good Morning Amine Jerbi
Thanks again for your help

Could you give me an example of how I could do it?

I'm still new to Power BI

[aj1973]

Hi

Is it possible to share a sample of your PBIX?

[ngomes]

Send the example with data

example.pbix 

[aj1973]

Here you go

https://drive.google.com/file/d/1m6-M_F2t0stLdxnOo90TdQSpKPnl1PN6/view?usp=share_link

[ngomes]

good afternoon again

That wasn't quite what I wanted, how to add the values of the previous year I know how to do it, I don't know how to compare the year 2021 with 2017 and 2017 with 2015, I don't know if I made myself understood, I send the print

the objective is to compare the 1st year selected with the 2nd selected and the 2nd with the 3rd

Thank you for your help