Calculated column to show current year unless it's january (then show last year)

[thalitakadlec]

Hi there,

Last year (2019) I created a dashboard which uses a calculated column to obtain the current year, with this formula:

= Date.Year(DateTime.LocalNow())

It was working fine until we turned from 2019 to 2020, but in January we are still reporting last December's metrics.

Therefore, I need to find a way what this formula shows me what year it is, unless we're in january, in which case I need it to show last year.

I imagine I could subtract 1 month from the current date but I am not sure how to formulate that.

Could someone help?

Thanks and happy new year!

[az38]

Hi @thalitakadlec 

if you mean Custom column (power qery editor), not calculated then try

= if Date.Month = 1 then Date.Year(DateTime.LocalNow())-1 else Date.Year(DateTime.LocalNow())

do not hesitate to give a kudo to useful posts and mark solutions as solution

[thalitakadlec]

Yes custom column!

I have tried your suggestion, PowerBI accepted the formula, however I'm still getting 2020 on the Year column.

Any idea why this didn't work?

[ChrisMendoza]

@thalitakadlec -

You're not providing the Date.Month ( [ ?] ) .

[az38 · Accepted solution]

oh, sorry @thalitakadlec  my bad

try

= if Date.Month(DateTime.LocalNow()) = 1 then Date.Year(DateTime.LocalNow())-1 else Date.Year(DateTime.LocalNow())

do not hesitate to give a kudo to useful posts and mark solutions as solution

[thalitakadlec]

Awesome it worked, thank you!