Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Tlotly
Resolver II
Resolver II

Create a custom column containing a variable

Good day

Please assist.

 

How do I create a column in powerquery with the following DAX?

DefaultYear = if(YEAR(TODAY()) = 'Branch Scores'[FinYear],
"DefaultYear"
,
'Branch Scores'[FinYear])

 

 

Kind regards

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @Tlotly ,

 

Create a column as below:

 

=if [Year]=Date.Year(DateTime.LocalNow()) then "Default Year" else  [Year]

vkellymsft_0-1626150755957.png

For my sample .pbix file ,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

Hi @Tlotly ,

 

Create a column as below:

 

=if [Year]=Date.Year(DateTime.LocalNow()) then "Default Year" else  [Year]

vkellymsft_0-1626150755957.png

For my sample .pbix file ,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Anonymous
Not applicable

PriorStepName should be replaced with YOUR actual prior step name (the name of your previous step.

Anonymous
Not applicable

Try this:

DefaultYear = Table.AddColumn (PriorStepName, "Year", each if Date.IsInCurrentYear([FinYear]) then "DefaultYear" else [FinYear])
 
--Nate
,
'Branch Scores'[FinYear])

 

@Anonymous  Thank you. It's complaining about PriorStepName not being recognised. 

 

Tlotly_0-1626076666096.png

 

mahoneypat
Microsoft Employee
Microsoft Employee

You can try this expression in a custom column, assuming your FinYear column is type date.

 

= if [FinYear] = Date.From(DateTime.LocalNow()) then "Default Year" else [FinYear]

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat  Thank you. I tried the above code but it doesn't work for financial dates since my slicer is based on the financial period.  E.g. My Financial dates are from July to June so when I get to 01 July 2021, it will still default to 2021 as the code you sent is comparing to today. I will want that to default to the new financial year which will be 2022.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.