topic Re: Handle blank date in fiscal year calculation in DAX Commands and Tips

Handle blank date in fiscal year calculation

PAPalmer — Sun, 25 Feb 2024 20:17:19 GMT

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

Re: Handle blank date in fiscal year calculation

lbendlin — Mon, 26 Feb 2024 01:01:38 GMT

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.

Re: Handle blank date in fiscal year calculation

Anonymous — Mon, 26 Feb 2024 02:05:12 GMT

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!

Re: Handle blank date in fiscal year calculation

PAPalmer — Sat, 02 Mar 2024 04:20:52 GMT

Thank you Yang, it worked well

Re: Handle blank date in fiscal year calculation

PAPalmer — Sat, 02 Mar 2024 04:23:28 GMT

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