Forum Discussion

ngomes's avatar
ngomes
Icon for Helper II rankHelper II
3 years ago
Solved

DAX selected years

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

Need Help
  • ngomes's avatar
    ngomes
    3 years ago

    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

14 Replies

  • ngomes's avatar
    ngomes
    Icon for Helper II rankHelper II
    3 years ago

    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's avatar
      aj1973
      Icon for Community Champion rankCommunity Champion
      3 years ago

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

       

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    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's avatar
      ngomes
      Icon for Helper II rankHelper II
      3 years ago
      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
      • aj1973's avatar
        aj1973
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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