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 , use either if statement or switch statement in a new measure where if the year matches year(today()),i.e. 2023 it gives salesTable2 else salesTable1

.

Anne14's avatar
Anne14
Frequent Visitor
2 years ago

ok thanks but there is the condition about what month we are... il it is before mai or after that...

Can you give me an example for the full expression formula please ?

Thanks

 

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

    Anne14 Add the condition where if it's not this year it checks whether it is year(today()) - 1 and month(today()) < 5.

    Something like this:

    Measure = SWITCH(TRUE(),
        values('TableName'[Year]) = year(today()), [SalesTable2],
        values('TableName'[Year]) = year(today())-1 && MONTH(today()) < 5, [SalesTable2], [SalesTable1])

    This will return error if nothing is selected.
     
    Thank You.
    • Anne14's avatar
      Anne14
      Frequent Visitor
      2 years ago

      Hello,

       

      Thanks a lot for your answer. I used the If function instead of switch and it is ok but I have two types of pages. One page where I have a year slicer so I can select 2021 or 2022 or 2023 etc and on the table I have the product and Sales: here all works fine but I have another page where I have a pivot table with product as line and year as column (always sales as measure). 

      But when I try to make this table I have error message (as you mentionned that This will return error if nothing is selected) that columns contain multiples where 1 was expected which is normal because I try to select 2 or more years and the IF is waiting for one... How I can make another formula with the same function but who is showing the result for the 2 Salestable1 and Salestable2 in the same table 

       

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

      Here is the result I expect

       

       

                  Year

                             2021           2022          2023

      Product 1 Salestable1 Salestable1 Salestable2

      Product2   Salestable1 Salestable1 Salestable2

       

      thanks again

      • 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.