Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Multiple years & months in table

Hello Community -  Not sure how to fix this issue. 

 

Very simply, what I am trying to achieve is to have Total YTD sales and Total YTD last year sales on a table.   With the years as columns, and months as rows.  

 

It works....except for I try to select more than one year.   Then I get the error below.    

 

I do NOT have a bidirectional relationship on my date table.  I am not using direct query.    My date table is marked as a date table. 

 

Total Sales YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR('Date Table'[Date]))

 

 

 

If I were to select another year in my Year filter...I will get this message.   The visual seems to only work when one year is selected.

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous 

    I would use matrix and use months in Rows sesction and use Year in Columns section.

    If you don't have columns as years and months create as below.

    Months = format('Date Table'[Date],"mmmm")

    Years = YEAR('Date Table'[Date])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Yes, that is exactly what I have already.   I have a date table.    And I have months as rows, years as columns.  

       

      Works perfectly fine if just one year is seletected in the filter.   But if I try to select two years...I get that error. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 

    That is how is the SAMEPERIODLASTYEAR() function works, which returns data from the previous one year only.

    SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Docs

     

    It cannot recognize which year does the previous year based on if you select both 2019 and 2020, is it 2018 or 2019?


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.