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

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