cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

How To create Date Column From Month and Year column 2018-19 in Power Query

Hi  I am New in Power BI  Please help to me How To create Date Column From Month and Year column 2018-19 in Power Query .

 Business Year Month Quarter Division Qty State Branch A 2018-19 Apr Q1 2B 1 Andhra Pradesh South A 2018-19 Apr Q1 2B 3 Bihar East A 2018-19 Apr Q1 2B 5 Chandigarh North 1 A 2018-19 Aug Q2 2B 11 Kerala South A 2019-20 Apr Q1 2B 111 Bihar East A 2019-20 Apr Q1 2B 111 Chandigarh North 1 A 2019-20 Jan Q4 2B 123 Andhra Pradesh South A 2019-20 Jan Q4 2B 123 Bihar East A 2020-21 Jan Q4 2B 124 Andhra Pradesh South A 2020-21 Jun Q1 2B 234 Delhi North 1 A 2021-22 Jul Q2 2B 321 Chhattisgarh Central
2 ACCEPTED SOLUTIONS
Super User

Hi @shri0025 ,

Try adding a custom column in Power Query and using this calculation:

``````Date.From(
Text.Combine(
{
"01",
[Month],
if List.Contains({"Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, [Month])
then Text.Start([Year], 4)
else Text.Combine({"20", Text.End([Year], 2)})
}
)
)``````

I'm assuming that your [Year] field shows financial year, and that your financial year start is 1st April.

This gives me the following output:

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Super User

Hi @shri0025 ,

Try this new column:

``````Date.From(
Text.Combine(
{
"01",
[Month],
if
(not List.Contains({"Jan", "Feb", "Mar"}, [Month])
and Date.Month(DateTime.LocalNow()) >= 4)
or
(List.Contains({"Jan", "Feb", "Mar"}, [Month])
and Date.Month(DateTime.LocalNow()) < 4)
then
Text.From(Date.Year(DateTime.LocalNow()))
else if
not List.Contains({"Jan", "Feb", "Mar"}, [Month])
and Date.Month(DateTime.LocalNow()) < 4
then
Text.From(Date.Year(DateTime.LocalNow()) - 1)
else
Text.From(Date.Year(DateTime.LocalNow()) + 1)
}, "-"
)
)``````

I've tried to make this as dynamic as possible, so the date years will increase by 1 on 1st April each year, on the assumption that this table only ever holds current FY data.

I get the following output:

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

7 REPLIES 7
Super User

Hi @shri0025 ,

Try adding a custom column in Power Query and using this calculation:

``````Date.From(
Text.Combine(
{
"01",
[Month],
if List.Contains({"Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, [Month])
then Text.Start([Year], 4)
else Text.Combine({"20", Text.End([Year], 2)})
}
)
)``````

I'm assuming that your [Year] field shows financial year, and that your financial year start is 1st April.

This gives me the following output:

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helper II

Hi Thanks one More issue face  My one table only month  please help  how to create date column

 Business Month Division Qty Branch A Apr 2B 1 South A May 2B 3 East A Jun 2B 5 North 1 A Jul 2B 11 South A Aug 2B 111 East A Sep 2B 111 North 1 A Oct 2B 123 South A Nov 2B 123 East A Dec 2B 124 South A Jan 2B 234 North 1 A Feb 2B 321 Central A Apr 2B 5 South A May 2B 15 East A Jun 2B 25 North 1 A Jul 2B 55 South A Aug 2B 555 East A Sep 2B 555 North 1 A Oct 2B 615 South A Nov 2B 615 East A Dec 2B 620 South A Jan 2B 1170 North 1 A Feb 2B 1605 Central
Super User

Hi @shri0025 ,

In order to be a valid date field, you must have a year.

You can use this in a new custom column to convert your months to the first of the month in current year:

``Text.Combine({"01", [Month]}, "-")``

It will create a text field of day-month, but when you change the data type to Date you'll get the following output:

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helper II

Hi  Great work  some  below data FY 2021-22   Jan month show  Jan 21 but i need jan 22 show please help

Super User

Hi @shri0025 ,

Try this new column:

``````Date.From(
Text.Combine(
{
"01",
[Month],
if
(not List.Contains({"Jan", "Feb", "Mar"}, [Month])
and Date.Month(DateTime.LocalNow()) >= 4)
or
(List.Contains({"Jan", "Feb", "Mar"}, [Month])
and Date.Month(DateTime.LocalNow()) < 4)
then
Text.From(Date.Year(DateTime.LocalNow()))
else if
not List.Contains({"Jan", "Feb", "Mar"}, [Month])
and Date.Month(DateTime.LocalNow()) < 4
then
Text.From(Date.Year(DateTime.LocalNow()) - 1)
else
Text.From(Date.Year(DateTime.LocalNow()) + 1)
}, "-"
)
)``````

I've tried to make this as dynamic as possible, so the date years will increase by 1 on 1st April each year, on the assumption that this table only ever holds current FY data.

I get the following output:

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helper II

Hi Thank you very much

Anonymous
Not applicable

try column from example

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors