Forum Discussion

paulfink's avatar
paulfink
Post Patron
5 years ago
Solved

Custom Date Field

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

6 Replies

    • paulfink's avatar
      paulfink
      Post Patron
      5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi paulfink ,

         

        Is the following result what you want?

         

        Best Regards,

        Yuna

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.