topic Re: Create formula switch measure depending on year selected in DAX Commands and Tips

Create formula switch measure depending on year selected

Anne14 — Wed, 04 Oct 2023 20:50:31 GMT

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

Re: Create formula switch measure depending on year selected

ChiragGarg2512 — Thu, 05 Oct 2023 09:36:10 GMT

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

Re: Create formula switch measure depending on year selected

Anne14 — Thu, 05 Oct 2023 08:14:58 GMT

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

Re: Create formula switch measure depending on year selected

ChiragGarg2512 — Thu, 05 Oct 2023 10:00:09 GMT

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.

Re: Create formula switch measure depending on year selected

Anne14 — Thu, 12 Oct 2023 16:26:58 GMT

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

Re: Create formula switch measure depending on year selected

ChiragGarg2512 — Fri, 13 Oct 2023 06:11:11 GMT

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.

Re: Create formula switch measure depending on year selected

Anne14 — Fri, 13 Oct 2023 08:13:18 GMT

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

Re: Create formula switch measure depending on year selected

ChiragGarg2512 — Fri, 13 Oct 2023 08:57:07 GMT

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.

Re: Create formula switch measure depending on year selected

Anne14 — Fri, 13 Oct 2023 10:02:14 GMT

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

Re: Create formula switch measure depending on year selected

ChiragGarg2512 — Fri, 13 Oct 2023 11:33:31 GMT

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