Convert Month & Fiscal year to Month & Fiscal year +1

[TuanNguyen0808]

Hi,

I often have to work with actual and budget data in Power BI. My budget data appears as the first screenshot where the fiscal year is from April to March containing budget figures in respective months, one column for fiscal year. That table contains budget data for multiple fiscal years.

During the course of data cleansing in Power Query, I have problem converting months from Jan to Mar.

For example, FY2023 & Jan should be come January 2024, not January 2023. So, I must save the result from Power Query to Excel, then insert another column named "Correct date" with a formula:

=IF(MONTH([@Date])<=3,[@Date]+366,[@Date])

(screenshot 2)

It's quite inconvenient.

I don't want to continue that method anymore. Can anyone help me how I can convert months from Jan to Mar to correct years using DAX in Power Query w/o another step in Excel?

Thanks in advance.

[ppm1 · Accepted solution]

I would encourage you to build your Date table the other way, starting with the calendar date and then calculating the fiscale date columns from that. But here is one way to do it from your existing columns. Put this expression into a custom column.

let
FD = Date.FromText([FY] & "-" & [Month] & "-" & "1"),
YearOffset = if Date.Month(FD)<=3 then 1 else 0,
OriginalDate = Date.ToText(FD, "MMMM") & " " & Text.From(Date.Year(Date.AddYears(FD, YearOffset)))
in
OriginalDate

Pat

[TuanNguyen0808]

Hi Pat,

This is the screenshot of the steps I did in Power Query using your suggestion. After inserting a custom column, the years are backward for Jan, Fed and Mar. I want the year value in circled part to move forward to 2023, not 2021.

Please help.

[ronrsnfld · Accepted solution]

Note that with the method below, the only columns required are your FY and Month columns

Custom Column:

let 
  dt = Date.FromText([Month] & " " & Text.From([FY]))
in 
  if Date.Month(dt) <=3 then Date.AddMonths(dt,12) else dt

[TuanNguyen0808]

This method works well. Thank you very much.

[ppm1 · Accepted solution]

I would encourage you to build your Date table the other way, starting with the calendar date and then calculating the fiscale date columns from that. But here is one way to do it from your existing columns. Put this expression into a custom column.

let
FD = Date.FromText([FY] & "-" & [Month] & "-" & "1"),
YearOffset = if Date.Month(FD)<=3 then 1 else 0,
OriginalDate = Date.ToText(FD, "MMMM") & " " & Text.From(Date.Year(Date.AddYears(FD, YearOffset)))
in
OriginalDate

Pat

[TuanNguyen0808]

Hi, Pat,

I have a Calendar table with the following code:

Calendar = ADDCOLUMNS(CALENDARAUTO(),

"Year", YEAR([Date]),

"Fiscal Year", IF(MONTH([Date]) <=3, Year([Date])-1, YEAR([Date])),

"Month number", MONTH([Date]),

"Period", IF(MONTH([DATE]) <=3,MONTH([Date])+9, month([Date])-3),

"Quarter", IF(MONTH([DATE])<=3,QUARTER([Date])+3,QUARTER([DATE])-1),

"Qtr", SWITCH(TRUE(),MONTH([date]) IN {4,5,6},"Qtr 1",MONTH([date]) IN {7,8,9},"Qtr 2",MONTH([date]) IN {10,11,12},"Qtr 3","Qtr 4"),

"Month name", FORMAT([Date],"MMM"))

Anyway, your solution today works well. Thank you very much.

[ppm1]

You can do it in Power Query with a custom column like this. Just shift the # of months forward or back based on your fiscal calendar.

= Date.ToText([Date], "MMMM") & " " & Text.From(Date.Year(Date.AddMonths([Date], -3)))

Pat