Forum Discussion

DemoFour's avatar
DemoFour
Continued Contributor
4 years ago
Solved

Create a Year Period column in M

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 

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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's avatar
      DemoFour
      Continued Contributor
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      DemoFour
      Continued Contributor
      4 years ago

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