Forum Discussion

Anne14's avatar
Anne14
Frequent Visitor
2 years ago

Create formula switch measure depending on year selected

Hello,   I need your help to create a dynamic measure which can be switched in a table automaticly by the user year selection.   Here is the details: I have 2 sorce tables which have the same col...
ChiragGarg2512's avatar
ChiragGarg2512
Icon for Solution Sage rankSolution Sage
2 years ago

Anne14 

IF( values('table1'[Year]) = year(today()) && MONTH(today()) > 5, [Salestable1], [Salestable2])

This will give SalesTable1 for october 2023.

 

Use an isfiltered function for [year], also specify what is the required value when two years are selected.

Anne14's avatar
Anne14
Frequent Visitor
2 years ago

hello

Thanks for your reply. 

Let say we are in onctober 2023 and that 2021,2022,2023 are selected The required values are Salestable1 in the column 2023 and Salestable2 in the column 2021 and 2022. 

(If we are in March 2023 in all the 3 columns I have to have Salestable2 )

Can you suggest me a formula?

Thanks

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Anne14 , This measure should work but introduce a date column somehow, because for multiple selection on slicer there needs to be a distinction. If the table visual just has productName and measure, then multiple selection is going to create a problem. In matrix visual, set rows as product, columns as year and values as measures. 

    • Anne14's avatar
      Anne14
      Frequent Visitor
      2 years ago

      that is what I did : try the matrix visual but I have the error that columns contain multiples where 1 was expected when I select 2 or more years in the slicer 😞 I think the IF function will always require one selected value.. I need some other solution 

      thanks

      • ChiragGarg2512's avatar
        ChiragGarg2512
        Icon for Solution Sage rankSolution Sage
        2 years ago

        if year is part of column fields, there would be no need of slicer. If slicer is needed, make a field parameter.