Forum Discussion
Calculated column in Calculated table only populates first 430ish rows. Help?
Hi All,
I'm having a very odd issue with Power BI.
I have a calculated date table created with the CALENDAR function to handle a range of dates. I then have a calculated field "Fiscal Year" which simply determines the fiscal year based on a month offset
Fiscal Year = year(dateadd('date table'[Date],'date table'[Fiscal Offset],Month)
The fiscal offset is 5 (August becomes period 1), and is populated for all rows. However the calculated Fiscal Year and Fiscal Month only calculate for the first 430ish rows and then are blank.
I'm sure I'm missing something stupid here. Any ideas / suggestions?
Thanks!
Garrett
Hi garrettw ,
The problem is with the DATEADD() function. The column will be blank value when the calculated date is greater than the maximum date in the table.
You could use EDATE() function instead.
Fiscal Year = YEAR(EDATE('date table'[Date],'date table'[fiscal offset]))Best Regards,
Jay
3 Replies
garrettw , In my FY blog, all shared all 12 calendars that can start with a different months. Check if that can help
Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441Thanks for the link -- lot of good information in there that I might use going forward, but in this case I need to create the table with the offset calculations for another reason (This client also has to provide reporting for their clients' fiscal years, so I have to generate the fiscal calendar data on the fly based on other data) -- so I don't think that will quite apply.
I don't think this issue is just limited to fiscal calendars -- it seems PowerBI is just decided at row 430 or so, "I'm just not going to do these calculations for any more rows". This is a table that will have other calculations in it so I need to figure out what I'm doing wrong or if this is a bug of some sort.
Thanks in any case, lots of good stuff in your blog post that will help me with other aspects of the reporting for them!
Hi garrettw ,
The problem is with the DATEADD() function. The column will be blank value when the calculated date is greater than the maximum date in the table.
You could use EDATE() function instead.
Fiscal Year = YEAR(EDATE('date table'[Date],'date table'[fiscal offset]))Best Regards,
Jay
