Custom Column Homologous Month

[gonrodrigues]

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 "PreviousMonth"? I have this custom columns:

I'm not familiar with DAX 😕

Thanks!

[Eric_Zhang · Accepted solution]

@gonrodrigues

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

[Greg_Deckler]

Can you explain what you want PreviousMonth to be? Do you want the actual previous month or do you want it to be a TRUE/FALSE value like CurrentMonth. And I have no idea what you are going for with HomologousMonth, is that the same as the current month in a different year? And is that also TRUE/FALSE or something else?

[gonrodrigues]

 Ok, there's a previous month Function and for homologous month? 

[Eric_Zhang]

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

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

[Eric_Zhang · Accepted solution]

@gonrodrigues

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

[gonrodrigues]

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 · Accepted solution]

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

[gonrodrigues]

Worked perfectly. Thanks a lot!

[gonrodrigues]

Hey @Greg_Deckler

I want both TRUE/FALSE. HomologousMonth would be the same month in previous year 🙂

Thanks