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

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