Create a Year Period column in M

[DemoFour]

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 

[watkinnc]

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

[DemoFour]

@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. 

[mahoneypat · Accepted 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

[DemoFour]

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