date status on query

[erhan_79]

hi there 

i have a table as below , and i want to create a new column which is calling Status Month as at right coumn

so the rule will be like that ,

İf document date 's month is on  the month of today's month , status will be "Actual Month " 

İf document date 's month includes previous month of  today's month , status will be "Previous Month " 

İf document date 's month includes later  month of  today's month , status will be "Next Month " 

i need two kind of formula , one of them at dax formula side , and the other one for query side

thanks in advance

[Fowmy · Accepted solution]

@erhan_79 

Power Query Solution,
Add New Column and paste this code block, Check the Document Date Column name and rename as necessary.

=if  Date.EndOfMonth([DATE]) = Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())) then "Current Month" else if 
Date.EndOfMonth([DATE]) = Date.AddMonths( Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),-1) then "Last Month" else if 
Date.EndOfMonth([DATE]) = Date.AddMonths( Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),+1) then "Next Month"
else null

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Fowmy · Accepted solution]

@erhan_79 

So all dates before the current month are Previous and all dates after the current month are Next Month: 

May be this is what you need.

Table.AddColumn(#"Renamed Columns", "Custom", each 
    
    if  Date.EndOfMonth([DATE]) = Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())) then "Actual Month" else if 

    Date.EndOfMonth([DATE]) <= Date.AddMonths( Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),-1) then "Previous Month" else if 
    Date.EndOfMonth([DATE]) >= Date.AddMonths( Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),+1) then "Next Month"
    
    else null)

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Fowmy · Accepted solution]

@erhan_79 

Power Query Solution,
Add New Column and paste this code block, Check the Document Date Column name and rename as necessary.

=if  Date.EndOfMonth([DATE]) = Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())) then "Current Month" else if 
Date.EndOfMonth([DATE]) = Date.AddMonths( Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),-1) then "Last Month" else if 
Date.EndOfMonth([DATE]) = Date.AddMonths( Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),+1) then "Next Month"
else null

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[erhan_79]

dear @Fowmy   ;

i noticed something wrong now , your formula calculating based on only year 2020

i need to calculate previous years and next yeears too also 

could you revise the formula pls 

[Fowmy]

@erhan_79 

If the date is not Last Month, Current Month or Next Month, what do want to call it ?

You can replace the null with whatever text you need.

I am comparing with the current date and its month

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS 👍 to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[erhan_79]

dear @Fowmy  ;

i think there is an misunderstanding ;

i can explain rule to understand better  like that ;

if document date has same month and year with today   new column will be "actual month"

if document date has previous month and years than today new column will be " previous month"

if document date has next month and years than today new column will be " next month"

EXAMPLES : 

today's month : July 2020

document date : 05.07.2022  status : next month (because after   july 2020)

document date 10.05.2021  status : next month (because  after july 2020) 

document date :10.07.2020  status : actual month  (because in july 2020) 

document date : 10.03.2019  status : previous month  (because before july 2020) 

document date 05.04.2020  status : previous month (because before july 2020)

Could you pls revise formula now

[Fowmy]

@erhan_79 

In your explanation, I think this has to be "Actual Month"

"document date : 05.07.2022  status: next month (because after   july 2020)"

[Fowmy · Accepted solution]

@erhan_79 

So all dates before the current month are Previous and all dates after the current month are Next Month: 

May be this is what you need.

Table.AddColumn(#"Renamed Columns", "Custom", each 
    
    if  Date.EndOfMonth([DATE]) = Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())) then "Actual Month" else if 

    Date.EndOfMonth([DATE]) <= Date.AddMonths( Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),-1) then "Previous Month" else if 
    Date.EndOfMonth([DATE]) >= Date.AddMonths( Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),+1) then "Next Month"
    
    else null)

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[erhan_79]

now everything is ok thanks @Fowmy 

[erhan_79]

thank you very much @Fowmy 

[Greg_Deckler]

@erhan_79 Well for DAX it should be along the lines of:

Status Month = 
  VAR __TodayMonth = MONTH(TODAY())
  VAR __Month = MONTH([Doc Date])
  VAR __PreviousMonth = 
    SWITCH(__Month,
      1,12,
      __Month - 1
    )
  VAR __NextMonth = 
    SWITCH(__Month,
      12,1,
      __Month + 1
    )
RETURN
  SWITCH(TRUE(),
    __Month = __TodayMonth,"Actual Month",
    __Month = __PreviousMonth,"Previous Month",
    __Month = __NextMonth,"Next Month",
    "Unknown"
  )

Probably have adjust to take years into account but that should be the general concept. The Power Query could be a bit more messy since there is no true switch statement so you will have to use if then else.

[erhan_79]

dear @Greg_Deckler 

thanks for your reply , but something wrong i think , as you see below only actual mkont info is coming others seems unknown 

could you pls help , or maybe i made a mistake 

[Greg_Deckler]

@erhan_79 - Whoops, slight logical error! 🙂

Status Month = 
  VAR __TodayMonth = MONTH(TODAY())
  VAR __Month = MONTH([Doc Date])
  VAR __PreviousMonth = 
    SWITCH(__TodayMonth,
      1,12,
      __TodayMonth - 1
    )
  VAR __NextMonth = 
    SWITCH(__TodayMonth,
      12,1,
      __TodayMonth + 1
    )
RETURN
  SWITCH(TRUE(),
    __Month = __TodayMonth,"Actual Month",
    __Month = __PreviousMonth,"Previous Month",
    __Month = __NextMonth,"Next Month",
    "Unknown"
  )

[erhan_79]

Dear @Greg_Deckler  ;

thanks for your kind support but as you will see below when i add other year date it is not working properly ;

i think there is an misunderstanding ;

i can explain rule to understand better  like that ;

if document date has same month and year with today   new column will be "actual month"

if document date has previous month and years than today new column will be " previous month"

if document date has next month and years than today new column will be " next month"

EXAMPLES : 

today's month : July 2020

document date : 05.07.2022  status : next month (because after   july 2020)

document date 10.05.2021  status : next month (because  after july 2020) 

document date :10.07.2020  status : actual month  (because in july 2020) 

document date : 10.03.2019  status : previous month  (because before july 2020) 

document date 05.04.2020  status : previous month (because before july 2020)

Could you pls revise formula now

[Greg_Deckler · Accepted solution]

@erhan_79 - 

Right, I said that you would probably need to account for year:

Status Month = 
  VAR __TodayMonth = MONTH(TODAY())
  VAR __TodayYear = YEAR(TODAY())
  VAR __Month = MONTH([Doc Date])
  VAR __PreviousMonth = 
    SWITCH(__Month,
      1,12,
      __Month - 1
    )
  VAR __NextMonth = 
    SWITCH(__Month,
      12,1,
      __Month + 1
    )
  VAR __MonthYear = YEAR([Doc Date])
  VAR __PreviousMonthYear = 
    SWITCH(__Month,
      1,12,
      __MonthYear - 1
    )
  VAR __NextMonthYear = 
    SWITCH(__Month,
      12,1,
      __MonthYear + 1
    )
RETURN
  SWITCH(TRUE(),
    __Month = __TodayMonth && __MonthYear = __TodayYear,"Actual Month",
    __Month = __PreviousMonth && __MonthYear = __PreviousMonthYear,"Previous Month",
    __Month = __NextMonth && __MonthYear = __NextMonthYear,"Next Month",
    "Unknown"
  )

[Fowmy]

@erhan_79 

It is possible but why would you need in Power Query and in the Model (DAX) both?. if you add in Power Query, you will have in the model as well.