Skip to main content
cancel
Showing results for 
Search instead 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.

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

Can some please help.

 

Thank you,

Pam

                      

 

 

 

1 ACCEPTED SOLUTION
v-huijiey-msft
Community Support
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!

View solution in original post

4 REPLIES 4
v-huijiey-msft
Community Support
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!

Thank you Yang, it worked well

lbendlin
Super User
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.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors