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

Question

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.

Will the year switch over in the new Calendar Year or do you have a different Fiscal Year start? — Sam Nseir
– Sam Nseir, Commented Feb 15, 2024 at 13:47

Sam Nseir · Accepted Answer · 2024-02-15 14:09:53Z

1

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

answered Feb 15, 2024 at 14:09

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related