Date table column with rolling years for each previous 365 days. How can i do that on Power BI?

[MonirSikder]

Hi all,

How can I create a column with rolling years for each previous 365 days. How can i do that on Power BI? last 365 as -Y1, followed by -Y2, -Y3 and it counts backwards.

[m_dekorte · Accepted solution]

Hi @MonirSikder ,

You could give something like this a go.

Just copy the script below into a new blank query.

let
  Source = Table.FromColumns(
    {List.Dates(#date(2021, 1, 1), 3 * 365, Duration.From(1))}, 
    type table [Date = date]
  ), 
  AddRollingYear = Table.AddColumn( Source, "Rolling Year", each 
    [ n = Number.Round( -Number.From( Date.From( DateTime.FixedLocalNow()) - [Date] )/ 365, 0), 
      r = if n < 0 then Text.From(n) & "Y" else null
    ][r], type text
  )
in
  AddRollingYear

It's relative to today, amend to your needs.