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.
I receive budget and actual amounts with assigned date ranges e.g. 2021/22, 2022/23 etc in their own "Financial Year" column, I am very new to Power Query and BI and would love to know how to these assigned dates to an actual fiscal year range. My financial year runs April to March
Solved! Go to Solution.
Hi @NatWinckler ,
Please new a blank query in Power Query Editor:
let
startDate = #date(2020, 1, 1), //start date. update the date if you need it
endDate = Date.From(DateTime.LocalNow()), //Today date
numMonths = Duration.Days(endDate - startDate), //get number of months
dateList = List.Dates(startDate, numMonths, #duration(1,0,0,0)), //generate a list of dates
generateCalendar = List.Transform(
dateList,
each
{
_, //date
Date.Day(_), //get day
Date.Month(_), //get month
Date.Year(_), //get year
Date.DayOfWeekName(_), //get day of week name
Date.MonthName(_), //get month name
( //get financial period
if Date.Month(_)-3 < 1
then Date.Month(_)+9
else Date.Month(_)-3
),
( //get financial year
if _ > #date(Date.Year(_),3,31)
then Date.ToText(_,"yyyy")&"/"&Date.ToText(Date.AddYears(_,1),"yy")
else Date.ToText(Date.AddYears(_,-1),"yyyy")&"/"&Date.ToText(_,"yy")
)
}
),
output = Table.FromRows(
generateCalendar,
{ //column name: same order as above
"Date",
"Day",
"Month",
"Year",
"Day Of Week",
"Month Name",
"Financial Period",
"Financial Year"
}
)
in
output
Refer:
Re: Fiscal Year / Calendar Year - Power Platform Community (microsoft.com)
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
Hi @NatWinckler ,
Please new a blank query in Power Query Editor:
let
startDate = #date(2020, 1, 1), //start date. update the date if you need it
endDate = Date.From(DateTime.LocalNow()), //Today date
numMonths = Duration.Days(endDate - startDate), //get number of months
dateList = List.Dates(startDate, numMonths, #duration(1,0,0,0)), //generate a list of dates
generateCalendar = List.Transform(
dateList,
each
{
_, //date
Date.Day(_), //get day
Date.Month(_), //get month
Date.Year(_), //get year
Date.DayOfWeekName(_), //get day of week name
Date.MonthName(_), //get month name
( //get financial period
if Date.Month(_)-3 < 1
then Date.Month(_)+9
else Date.Month(_)-3
),
( //get financial year
if _ > #date(Date.Year(_),3,31)
then Date.ToText(_,"yyyy")&"/"&Date.ToText(Date.AddYears(_,1),"yy")
else Date.ToText(Date.AddYears(_,-1),"yyyy")&"/"&Date.ToText(_,"yy")
)
}
),
output = Table.FromRows(
generateCalendar,
{ //column name: same order as above
"Date",
"Day",
"Month",
"Year",
"Day Of Week",
"Month Name",
"Financial Period",
"Financial Year"
}
)
in
output
Refer:
Re: Fiscal Year / Calendar Year - Power Platform Community (microsoft.com)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.