Status Of Month Calculating Error

[erhan_79]

Hi there ;

i have a calculation on power quaery side with a M formula 

it works like that   ; i have date column and i am creating status column based on that dates.

Rule :

if the date's month before the current month status = Previous Month 

İf the date's month is in current month status =Actual Month

İf the dates's month is after current month status = Next Month 

So all dates before the current month are Previous and all dates after the current month are Next Month , all the dates in current month are  Actual Month 

i am using below formula on power Query side and i need this formula on power query side : 

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

but as you see below  when i use like that formula for august month system gives "null" ,  how can i solve this problem could you check pls

[amitchandak · Accepted solution]

@erhan_79 , seems fine try like

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

[erhan_79]

thank you very much @amitchandak !

it is working now