Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
6 years ago
Solved

date status on query

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

 

Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    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's avatar
    Fowmy
    6 years ago

    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

  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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"
  )

14 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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's avatar
      erhan_79
      Post Prodigy
      6 years ago

      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's avatar
        Fowmy
        Super User
        6 years ago

        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

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      erhan_79
      Post Prodigy
      6 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        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"
  )