cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Handle blank date in fiscal year calculation

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())

Thank you,

Pam

1 ACCEPTED SOLUTION
Community Support

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!

4 REPLIES 4
Community Support

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!

Frequent Visitor

Thank you Yang, it worked well

Super User

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.

Frequent Visitor

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors