cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cre8ive
New Member

Conditional Column with values for fiscal year date ranges

How do I create a conditional column with values for fiscal year date ranges "9/1/2011 to 8/31/2012"?

1 ACCEPTED SOLUTION
HotChilli
Super User II
Super User II

Something like this:

if Date.Month([Column1]) < 9 then Date.Year([Column1]) else Date.Year([Column1]) + 1

Please check at your side because I just rushed it.

 

It's generally considered good practice to do this sort of thing in a date table and use a relationship to link to the data.  Plenty of resources out there. 

View solution in original post

4 REPLIES 4
HotChilli
Super User II
Super User II

Something like this:

if Date.Month([Column1]) < 9 then Date.Year([Column1]) else Date.Year([Column1]) + 1

Please check at your side because I just rushed it.

 

It's generally considered good practice to do this sort of thing in a date table and use a relationship to link to the data.  Plenty of resources out there. 

View solution in original post

It worked, thank you!!!  Good to know, I will look into date tables.

HotChilli
Super User II
Super User II

Could you provide more detail please?  Do you want to add a column in Power Query according to a date in your data already? or something else?

Yes, in Power Query, I have a Transaction Date column and want to add a column that calculates Fiscal Year based on the transaction date ranges.

 

In Excel I used the formula below, I tried to replicate it in a Conditional Column and  then tried copying it into a Custom Column, neither worked.

 

=TEXT(YEAR([@[Transaction Date]])+IF(MONTH([@[Transaction Date]])>8,1,0),0)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors