Error - 13 Periods in the Financial Year

[MA_NetworkRail]

Hi,

I'm hoping someone can assist me with a date issue in PowerBI (I am a beginner to PowerBI).

For some context, I am putting together a simple dashboard with a few line graphs showing how the level of debt has fluctuated over the years (i.e. y axis= grand total of debt , x axis= dates (period and year). 

The issue I am having is with the x axis- the dates are being taken from a column titled "Period" in an Excel spreadsheet where the dates are written in the format 201901 , (i.e 2019 is the year , 01 is the period). PowerBI was unable to recognise the numbers in this column as dates (assuming this is becuase it needs to be in the format YYYYMMDD) so I have used a formula to create a new column in PowerBI titled "Date", as follows:

= Date.From(Text.From([Period])&"01", "de-DE")

However, in a financial year there are 13 periods (i.e. 201913 would mean the 13th period of Financial Year 2019). Therefore, when I change the column type to Date in Power BI, the lines with the 13th period come up with error messages. 

I'm not sure what to do to get around this issue and hoping someone can assist.

Thank you.

[v-cgao-msft]

Hi @MA_NetworkRail ,

Should the 13th Periods be considered as the 1st Periods of the following year? 
If so, please try:

let 
period = Number.ToText([Period]), year = Text.Start(period, 4), 
month = Text.End(period, 2) 
in 
if month = "13" 
then Date.FromText(Text.From(Number.FromText(year) + 1) & "01" & "01", "de-DE") 
else Date.FromText(year & month & "01", "de-DE")

Output:

Best Regards,
Gao
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!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum