Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
6 years ago
Solved

New Colum

Hello,

I would like to transform this DAX code in an add column statement in M language

YearWeekNo = 
    VAR FirstDayOfWeek = 1 -- Use: 0 para Domingo, 1 para Segunda, ..., 6 para Sábado
    VAR FirstSundayReference = DATE(1990; 12; 30) -- Não Alterar
    VAR CalDate = 'Calendar'[Date] -- Data a computar
    VAR FirtWeekReference = FirstSundayReference + FirstDayOfWeek
    VAR YearWeekNumber = INT(DIVIDE(CalDate -FirtWeekReference;7))
RETURN
YearWeekNumber

 Thanks in advance

Need Help
  • Fcoatis's avatar
    Fcoatis
    6 years ago

    I just figured it out:

     

    let
        refDate = Date.AddDays(#date(1990,12,30), 1),
        Source = List.Dates(#date(2018, 1, 1), 730, #duration(1, 0, 0, 0)),
        toTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        colRename = Table.RenameColumns(toTable,{{"Column1", "Data"}}),
        typeChange = Table.TransformColumnTypes(colRename,{{"Data", type date}}),
        addYear = Table.AddColumn(typeChange, "Ano", each Date.Year([Data]), Int64.Type),
        addMonth = Table.AddColumn(addYear, "Mês", each Date.Month([Data]), Int64.Type),
        addMonthName = Table.AddColumn(addMonth, "Nome do Mês", each Date.MonthName([Data]), type text),
        addDay = Table.AddColumn(addMonthName, "Dia", each Date.Day([Data]), Int64.Type),
        addWeekCont = Table.AddColumn(addDay, "SemanaSeq", each Number.IntegerDivide(Duration.Days([Data]-refDate),7))
    in
       addWeekCont

    Thanks in advance 

3 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    Here you go. Something like this

    (Date.StartOfWeek([Date], 1) - Date.StartOfWeek(Date.StartOfYear([Date]), 1)) / #duration(7, 0, 0, 0)
    • Fcoatis's avatar
      Fcoatis
      Post Patron
      6 years ago

      Sorry,

      The DAX formula creates a sequential number of weeks since a predetermined date without starting over each year; like this:

       

      • Fcoatis's avatar
        Fcoatis
        Post Patron
        6 years ago

        I just figured it out:

         

        let
            refDate = Date.AddDays(#date(1990,12,30), 1),
            Source = List.Dates(#date(2018, 1, 1), 730, #duration(1, 0, 0, 0)),
            toTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            colRename = Table.RenameColumns(toTable,{{"Column1", "Data"}}),
            typeChange = Table.TransformColumnTypes(colRename,{{"Data", type date}}),
            addYear = Table.AddColumn(typeChange, "Ano", each Date.Year([Data]), Int64.Type),
            addMonth = Table.AddColumn(addYear, "Mês", each Date.Month([Data]), Int64.Type),
            addMonthName = Table.AddColumn(addMonth, "Nome do Mês", each Date.MonthName([Data]), type text),
            addDay = Table.AddColumn(addMonthName, "Dia", each Date.Day([Data]), Int64.Type),
            addWeekCont = Table.AddColumn(addDay, "SemanaSeq", each Number.IntegerDivide(Duration.Days([Data]-refDate),7))
        in
           addWeekCont

        Thanks in advance