Entering Formula for Months

[uk747]

I want to Extract a Month from a Date e.g. 20/01/2024 the column is called myDate

When I Click on Manage it opens Power Pivot so I entered a formula in a New Column =MONTH(qry1[myDate]) and it works fine it enters 1s for Jan 2 for Feb etc

However I wanted to format as January, February etc so I edited the formula to =FORMAT(MONTH(qry1[myDate]), "MMMM")
But instead of January for all Januarys Dates it Enters December and Februarys Dates it Enters January

PS would this have been better to do in the actual Power Query or in DAX
And if i wanted to use a Pivot Table, just drag the myDate Column 3 times and formatted in there

 

[shift-del]

Untested.

=FORMAT(qry1[myDate], "MMMM")

And if i wanted to use a Pivot Table, just drag the myDate Column 3 times and formatted in there

And if you wanted to use a pivot table just drag the myDate column ONCE and group by year, quarter, month.

 

[uk747]

Thanks

Do you what the formulas are if entered in DAX
I entered =MONTH(qry1[myDate]) single value for column myDate in table 'qry1' cannot be determined
or =FORMAT(qry1[myDate], "MMMM") and neither worked

Even for YEAR I entered
=YEAR(qry1[myDate]) and got a single value for column myDate in table 'qry1' cannot be determined
Although it did work with =YEAR(MAX(qry1[myDate])) although I only have 2024 dates

 

[uk747]

And if you wanted to use a pivot table just drag the myDate column ONCE and group by year, quarter, month.

I tried that but sometimes the extra grouped columns disappeared when refreshed

Also DO you know if any downside if I did it in Power Query as easier to do or better in DAX or in Power Pivot

 

[shift-del]

Do you what the formulas are if entered in DAX
I entered =MONTH(qry1[myDate]) single value for column myDate in table 'qry1' cannot be determined
or =FORMAT(qry1[myDate], "MMMM") and neither worked

I'm sorry. You have to wait for an DAXpert.

I tried that but sometimes the extra grouped columns disappeared when refreshed

That could happen if the date field can't be gouped anymore (like when the field contains text).

Also DO you know if any downside if I did it in Power Query as easier to do or better in DAX or in Power Pivot

I guess it doesn't matter. You could even go and insert a month field into the source data using a worksheet formula.

 

[uk747]

Thanks for your help