Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a table (Tier) where i'd like to add column based on CY Column with Month End Dates. I am using below formula if date is 1/31/2021 add Jan, if 2/28/2021 add in February....and YTD add YTD. However formula is not working can nayone help me with what i am doing wrong?
Solved! Go to Solution.
Hi, @dokat
Column =
SWITCH(
TRUE(),
[CY] = dt"2018-12-31", "2018",
[CY] = dt"2017-12-31", "2017",
[CY] = dt"2019-12-31", "2019",
[CY] = dt"2020-12-31", "2020",
[CY] = dt"2021-12-31", "last year",
[CY] = dt"2022-02-28", "last month"
)
Result:
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @dokat
Column =
SWITCH(
TRUE(),
[CY] = dt"2018-12-31", "2018",
[CY] = dt"2017-12-31", "2017",
[CY] = dt"2019-12-31", "2019",
[CY] = dt"2020-12-31", "2020",
[CY] = dt"2021-12-31", "last year",
[CY] = dt"2022-02-28", "last month"
)
Result:
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@dokat , You are trying to do two thing here.
New column
Month Name = format([Date], "mmm")
YTD for this year as column
YTD = if([Date] >= date(year([Date]),1,1) && [Date] <= today() , "YTD", "Not YTD" )
Also refer
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
@amitchandak I am using below formula toc aptire last year but it looks up for 1/1/2021 and not 12/31/2021. Is there a way to modify where it looks up last date of the year? Thanks
@amitchandak formula returned not right values
Ultimately 'Tier'[CY] = 12/31/2021 then return "last year" in the new colun
If 'Tier'[CY] = 12/31/2021 return last year
if 'Tier'[CY] = 2/28/2022 return last month
if 'Tier'[CY] =12/31/2018 return 2018
if 'Tier'[CY] =12/31/2017 return 2017
if 'Tier'[CY] =12/31/2019 return 2019
if 'Tier'[CY] =12/31/2020 return 2020
Please see below screen shot fo rthe results of the formula. Thanks
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
99 | |
87 | |
45 | |
43 | |
35 |