Forum Discussion

gonrodrigues's avatar
gonrodrigues
Helper III
10 years ago
Solved

Custom Column Homologous Month

Hello,   To create colum Current Month I used this:   Table.AddColumn(MonthName, "CurrentMonth", each Date.IsInCurrentMonth( [Date] )) and if I want to creat two more: "HomologousMonth" and "Pre...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    gonrodrigues

     

    They're DAX expressions, I was not using Power Query. Is Power Query necessary?

  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    gonrodrigues

     

    You can get the HomologousMonth with Date.IsInCurrentMonth as well.  Just add one year to [Date] and predicate it with IsInCurrentMonth.

    Table.AddColumn(PreviousMonth, "HomologousMonth", each Date.IsInCurrentMonth( Date.AddYears([Date],1) ))



    If you have any question, feel free to let me know.

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

gonrodrigues

Your requirement is not quite clear for me, please check if below DAX expressions work.

CurrentMonth = YEAR([date])=YEAR(TODAY()) && MONTH([date])=MONTH(TODAY())

previousMonth = YEAR([date])=YEAR(TODAY())&&MONTH([date])+1=MONTH(TODAY())

HomologousMonth = YEAR([date])+1=YEAR(TODAY())&&MONTH([date])=MONTH(TODAY())


 

If you have any question, feel free to let me know.

gonrodrigues's avatar
gonrodrigues
Helper III
10 years ago

Yes, it works! But those 'New Columns' are not usable in Power Query? Eric_Zhang

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    gonrodrigues

     

    They're DAX expressions, I was not using Power Query. Is Power Query necessary?

    • gonrodrigues's avatar
      gonrodrigues
      Helper III
      10 years ago

      Is necessary to integrate with this expression:

       

      Table.AddColumn(PreviousMonth, "MonthSlicer", each 
if [CurrentMonth] then "Current Month" 
else if [PreviousMonth] then "Previous Month" 
else [Month])

      I already have current Month:

       

      Table.AddColumn(MonthName, "CurrentMonth", each Date.IsInCurrentMonth( [Date] ))

      and Previous Month:

       

      Table.AddColumn(CurrentMonth, "PreviousMonth", each Date.IsInPreviousMonth( [Date] ))

       

      I don't know if it was strictly necessary but i did it this way.

       

      Thanks Eric_Zhang

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        10 years ago

        gonrodrigues

         

        You can get the HomologousMonth with Date.IsInCurrentMonth as well.  Just add one year to [Date] and predicate it with IsInCurrentMonth.

        Table.AddColumn(PreviousMonth, "HomologousMonth", each Date.IsInCurrentMonth( Date.AddYears([Date],1) ))



        If you have any question, feel free to let me know.