Create New Financial Year , Month, Quarter columns in Date table

[damit23183]

HI,

I do have Date Table which has regular calendar set up from January to December.

However, my client's financial year start from Oct. So i would like make columns with new data in.

What I have :

  

What I am looking for:

Forgot to add FY Month ...I am also looking for FY month column.

Thanks

[Ashish_Mathur]

Hi,

Try these calculated column formulas

Quarter = if(month(calendar[date])>=10,1,if(month(calendar[date])>=7,2,if(month(calendar[date])>=4,3,4)))

FY = if(month(calendar[date])>=10,year(calendar[date])+1,year(calendar[date]))

Hope this helps.

[damit23183]

Hi Ashish,

Thank you for your response.

I tried your formula but didnt work  for QuARTER.

Also, for months what will be dax formula?

Thanks

[amitchandak]

@damit23183 ,

I have a blog for that for any month. You can refer to this :

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

I have attached Oct-Sep Calendar. You can get any other month FY from Blog

[damit23183]

Hi Amit,

Thank you for your response and links it was usefull but didnt work, also not much clear on what has been done there PBIX file. 

I went through and applied same logic but does not look like its working as expected.

Is there anyway to have simplified DAX formula ?

Really appreciate your response.

Thanks

[damit23183 · Accepted solution]

Hi All,

I got the solution just use and mix up formula from Amit and Ashish which worked perfectly fine.

Just changed and play around DAX formula that worked.

Thanky you guys.

[Anonymous]

Can you post the solution you found? 

[damit23183]

Hi,

Please refer link sent by Amit Chandak  as well as formula given by Ashish on this post.

Thanks