Handle blank date in fiscal year calculation

[PAPalmer]

Hello, 

I am a newbie to Power Bi and seeking help from the experts.
I have a Class Dt which is converted to Fiscal date (which starts from Oct). 

using 

Fiscal date=If(MONTH(Table[Class_Dt])>10, YEAR(Table[Class_Dt])+1,Year(Table[Class_Dt]))

return

date(eoy,10,1) 

which is a date data type.

The Class_Dt has blank dates i

and hence it is inserting dates with year 1900 in the Fiscal date. I would like to modify this calculation to to include If ( ISBLANK(Table[Class_Dt], BLANK())

Can some please help.

Thank you,

Pam

[v-huijiey-msft · Accepted solution]

Hi @PAPalmer ,

Based on your description, it seems you are encountering an issue where blank dates in your column are resulting in fiscal dates with the year 1900.

Here is a revised version of your DAX formula:

Fiscal Date =

VAR IsDateBlank = ISBLANK(Table[Class_Dt])

RETURN

IF(

     IsDateBlank,

     BLANK(),

     IF(

         MONTH(Table[Class_Dt]) > 10,

         DATE(YEAR(Table[Class_Dt]) + 1, 10, 1),

         DATE(YEAR(Table[Class_Dt]), 10, 1)

     )

)

If you have any further questions please feel free to contact me, I would be grateful if you could provide sample data in your reply to me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[PAPalmer]

Thank you Yang, it worked well

[lbendlin]

You will find that the easiest solution is to use a proper calendar table including all your fiscal columns, prepared and stored externally (for example an Excel file on a SharePoint).  This data is immutable and there is no good reason to try to create it in either Power Query or in DAX.

[PAPalmer]

Thank you lbendlin. I will be utilizing your approach on a different dashboard for time series analysis which will have multiple fiscal year.