Custom Date Field

[paulfink]

Hi guys,

I need some help creating a custom date field that goes off of our Financal Year (June - May)

I have in mind:

• >May 21
• June - Dec 21
• 2022
• 2023<

Years will change depending on current date.

I had something along the lines of - if month < may and year is today = "> May 21"

etc etc

If we past May 21 then all years should be forwarded by 1

Is this possible?

This is what i had in mind:

As you can see all dates during May are in the > May 2021 Field - All dates from June onwards up to December are in the June - Dec 2021 Field. 2022 and 2023 will just be the next following years e.g if the current year is 2030 - they will be 2031 and 2032.

In a way, you could see this as an automated Group.

I do not want to create a Group as we would have to manually change this.

[Anonymous · Accepted solution]

Hi @paulfink ,

Based on your description, you can create a calculated column as dollows.

Column = 

IF (

 MONTH ( 'calendar'[Date] ) < 5

 || MONTH ( 'calendar'[Date] ) = 5

 && [Date].[Day] <= 21,

 MONTH('calendar'[Date])&"/"&DAY('calendar'[Date])&"/"&YEAR('calendar'[Date]),

 IF ( MONTH ( 'calendar'[Date] ) = 5 && [Date].[Day] > 21,">May "&(1+YEAR('calendar'[Date])), "June-Dec "&YEAR('calendar'[Date])+1 )

)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @paulfink ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please explain with an example. Can you kindly share some sample data and the expected result to have a clear understanding of your question? You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

Please see this post regarding How to Get Your Question Answered Quickly: 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,

Yuna

[paulfink]

edited my original post

[Anonymous]

Hi @paulfink ,

Is the following result what you want?

Best Regards,

Yuna

[amitchandak]

@paulfink , Not very clear what you, If you need FY calendar, Jun _may., Get from my blog

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

[paulfink]

I have a FY column on my calendar table already - our FY is from Start of June to End of May next year.