Converting Date and Time to Financial Year and Quarter/Month (Australian Date)

[shakir99]

Hi All,

I currently have a column (StartDate) that has values in the following format:

DD/MM/YYYY HH:MM:SS AM/PM
E.g. 14/08/2022 2:00:00 PM

I was hoping to accomplish conversions through DAX commands but wasn't really sure how to approach it, 

New Column 1 (Month & Financial Year) 

14/08/2022 2:00:00 PM --> Aug FY22

New Column 2 (Quarter and Financial Year)
14/08/2022 2:00:00 PM --> Q1 FY22

Any help or guidance would be appreciated!

[Arul]

@shakir99 ,

For new column 1 you can use below to queries and create calculated column,

FY = IF(MONTH('Time'[Date])>=7,YEAR('Time'[Date]),YEAR('Time'[Date])-1)

Month FY = FORMAT('Time'[Date],"MMM") &" "&"FY"& RIGHT('Time'[FY],2)

for new column 2 you can refer the below thread,

https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/m-p/56337 

Thanks,

Arul 

[shakir99]

Hi @Arul 
Thanks for your answer. 

This solution, "FY = IF(MONTH('Time....."

Works fine however shows blank values in 'Time'[Date] as 1899. Is there a way to instead have that value as "Blank"

Regarding the next solution, "Month FY = FORMAT...."

This is working fine as well but is showing that FY99 value because of the 1899 value in the FY column.

Again thank you for the solutions however how'd I replace the 1899 with blank instead, and also have that be the case for the second column 

[Anonymous]

Hi @shakir99,

In fact, 12/31/1899 should be the default value of date fields, they will be displayed if the value does not include the date part or stored the blank values.

You can add an if statement condition to check if the current date field value is blank or less than particular values to prevent the calculation of wrong date ranges.

Regards,

Xiaoxin Sheng

[shakir99]

Thanks @Anonymous , would it be possible to show some example code? I'm under the understanding that you'd use if (<condition>, [table]FY,(I'd normally put Blank() here however that would result in 'blank' appearing on output visualisation/table))

So still a little confused on how to not show blanks or the 1899's in the output value. 

[Anonymous]

Hi @shakir99,

Can you please share some dummy data that keep the raw data structure? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng