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 column "Products" and a column "Year" and for each table there is 1 measure column SalesTable1 and 1 measure column SalesTable2.

 

So on the page I want to display a slicer Year selection and a simple table with Product as a dimension and Sales like a measure but here is the conditions:

If Today we are in the year 2023 so If the user chose 2022,2021,2020 etc I need to display SalesTable1 and If the user chose the current year (2023) I need to display SalesTable2

 

The next year if today we are 01/05/2024 

and the Year chosen in the slicer is .....2021or 2022 or 2023 - I have to display SalesTable1 and if the year chosen is 2024 - SalesTable2

 

But if today we are in the range between 01/01/2024 and 30/04/2024 

For the year till 2022 - SalesTable1 and for 2023 and 2024 - SalesTable2

 

Thanks a lot for your help Hope it is clear

 

 

9 Replies

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