Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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?
You're not providing the Date.Month ( [ ?] ) .
Proud to be a Super User!
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
Awesome it worked, thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |