Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi ,
I have a table with Date, FinancialPeriod (= month) and FinancialYear from entries.
The accounting software uses FP and FY as key parameters
Some Dates are wrongly entered : they do not match the FinancialPeriod : example:
Date | FinancialPeriod | FinancialYear | Day | |
28/5/2017 | 6 | 2017 | 28 | not okay |
3/10/2017 | 11 | 2017 | 3 | not okay |
31/5/2017 | 5 | 2017 | 31 | ok |
where [Day] is:
#"Inserted Day" = Table.AddColumn(#"Changed Type1", "Day", each Date.Day([Date]), Int64.Type)
In order to correct this I create an AltDate comprising of :
#"Added AltDate" = try Table.AddColumn(#"Inserted Day", "AltDate", (each #datetime([FinancialYear],[FinancialPeriod],[Day],0,0,0)), type datetime)
However - this works fine for most of the dates but produces <1% errors for the ones listed as below:
Date | FinancialPeriod | FinancialYear | Day | AltDate | AltDate valid |
28/5/2017 | 6 | 2017 | 28 | 28/6/2017 | 28/6/2017 |
3/10/2017 | 11 | 2017 | 3 | 3/11/2017 | 3/11/2017 |
31/5/2017 | 6 | 2017 | 31 | error | 28/6/2017 |
31/10/2017 | 11 | 2017 | 31 | error | 28/11/2017 |
31/1/2017 | 2 | 2017 | 31 | error | 28/2/2017 |
For the AltDate it is important that the AltDate is in the FinancialPeriod and FinancialYear - the day may deviate eg 3 days
Thus if we say that in case of error we substract 3 days from Day : [Day-3] then it should always work eg 31-3=28 (works also in february)
My approach was to use try [expression [Day] ] otherwise [expression including Date.AddDays([Day],-3]) like this:
#"Added Custom" = try Table.AddColumn(#"Inserted Day", "AltDate1", (each #datetime([FinancialYear],[FinancialPeriod],[Day],0,0,0)), type datetime) otherwise Table.AddColumn(#"Inserted Day", "AltDate1", (each #datetime([FinancialYear],[FinancialPeriod],Date.AddDays([Day],-3),0,0,0)), type datetime)
but this does not work:
the error in the column (for <1% of all AltDate values) is:
"Expression.Error: The DateTime operation failed because the resulting value falls outside the range of allowed values."
Any help appreciated !
Solved! Go to Solution.
A few alterations should fix it:
#"step" = Table.AddColumn(#"Added Custom1", "AltDate1", each try #date([FinancialYear], [FinancialPeriod], [Day])
otherwise Date.EndOfMonth(#date([FinancialYear], [FinancialPeriod], 1)))
This will put the end of the month in but you can change it if you want to stick with the (subtract 3 days) method
A few alterations should fix it:
#"step" = Table.AddColumn(#"Added Custom1", "AltDate1", each try #date([FinancialYear], [FinancialPeriod], [Day])
otherwise Date.EndOfMonth(#date([FinancialYear], [FinancialPeriod], 1)))
This will put the end of the month in but you can change it if you want to stick with the (subtract 3 days) method