Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have been learning M with the help of blogs to build a dimension table. I want to create a column for FY = 2020 - 2021
I can do this in Dax with
Year Period =
IF(
'Date'[Quarter Number] >= 4 ,
'Date'[Year] - 1 & "-" & 'Date'[Year],
'Date'[Year] & "-" & 'Date'[Year] +1
)
But if I do this in M then it does not like the & "-" & part
I guess this is an easy. . . but I am new to M, but any help welcome on how to join the years with the + or - 1 please.
Thanks
Solved! Go to Solution.
The equivalent M syntax would look like this (assuming your Date table is created with a query and is not a DAX table). This also assumes your Year and Quarter columns are numbers (not text). Note that M is case sensitive.
Year Period =
if
[Quarter Number] >= 4 then
Text.From([Year] - 1) & "-" & Text.From([Year]) else
Text.From([Year]) & "-" & Text.From([Year] +1)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Try:
Year Period = VAR ly = TEXT(Date[Year]-1) VAR ny = TEXT(Date[Year]-1) VAR ty = TEXT(Date[Year] RETURN
IF Date[Quarter Number] >= 4, ly&"-"&ty,
ty&"-"&ny
--Nate
@watkinnc Thank you Nate, this did not work as written, but I will have an explore and see what I can do with your offered solution.
Have a good day.
The equivalent M syntax would look like this (assuming your Date table is created with a query and is not a DAX table). This also assumes your Year and Quarter columns are numbers (not text). Note that M is case sensitive.
Year Period =
if
[Quarter Number] >= 4 then
Text.From([Year] - 1) & "-" & Text.From([Year]) else
Text.From([Year]) & "-" & Text.From([Year] +1)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Morning @mahoneypat
Thank you for the solution.
Because I had made the Quarter Column with:
= "Q" & [Quarter Number]
I just tried the same with the column names and & but I can see now that you need to use Text.From to tell the engine to take the value from the column and - 1
Thank you for posting up, have a good day Pat