topic Re: Converting Date and Time to Financial Year and Quarter/Month (Australian Date) in DAX Commands and Tips

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

shakir99 — Tue, 31 May 2022 02:54:53 GMT

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!

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

Arul — Tue, 31 May 2022 05:22:40 GMT

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

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

shakir99 — Thu, 02 Jun 2022 02:13:01 GMT

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

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

Anonymous — Tue, 07 Jun 2022 06:50:41 GMT

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

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

shakir99 — Wed, 08 Jun 2022 00:09:59 GMT

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.

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

Anonymous — Thu, 30 Jun 2022 09:07:37 GMT

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