[SOLVED] Dynamic year-values when creating a customized column in power bi depending on the actual year

Dynamic year-values when creating a customized column in power bi depending on the actual year

I want to aggregate three timebuckets to one. At the moment I create a customized column like this:

timebucketNEW = IF([timebucket] IN {"YtD", "BoY", "2023"}, "2024", [timebucket])

This is already working well for this year. But in 2025 the IF-clause should look like this:

timebucketNEW = IF([timebucket] IN {"YtD", "BoY", "2024"}, "2025", [timebucket])

Now, I don't want to change this IF-statement in each new year. How can I do this automatically or dynamically, like System_Year()-1 gives me the last year and System_Year() gives me the actual year.

EDIT: The year in the timebucket column is expanded by another year with each new year. The other previous years remain included in the column or data set.

Solution

Two options for you.

Similar to @davidebacci answer, try (formatting to text solves it):

timebucketNEW =
  IF(
    [timebucket] IN {"YtD", "BoY", FORMAT(YEAR(TODAY())-1, "0")}, 
    FORMAT(TODAY(), "yyyy"),
    [timebucket]
  )

Alternatively, you may want to have a dedicated table for this.
Create a new Calculated Table with:

Dim Year = 
  ADDCOLUMNS(
    DISTINCT('YourTable'[timebucket]),
    "Year",
      IF(
        [timebucket] IN {"YtD", "BoY", FORMAT(YEAR(TODAY())-1, "0") }, 
        FORMAT(TODAY(), "YYYY"),
        [timebucket]
      )
  )

Then add a relationship between this new table and YourTable on the [timebucket] columns. You would then use this new table for slicers, filters, visual dimension etc...