Forum Discussion

RUION's avatar
RUION
Frequent Visitor
8 years ago
Solved

IF formula for future months

Hi, 

 

I am struggling to create a new column with a formula that displays if the LoadgDate is a future month or past/current month. 

I am using below formula and for some reason the syntax is incorrect.  Can someone please take a look and advise me? 

 

New Column =

IF

 (OR

       (

         YEAR([Loadg Date]) > YEAR(TODAY()), MONTH([Loadg Date]) >MONTH (TODAY())

       ),

  "FutureMonth","PastOrCurrent")

)

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi RUION,

     

    Try to simplify your formula using this:

     

    Future/Past =
IF (
    EOMONTH ( Table1[Date]; 0 ) > EOMONTH ( TODAY (); 0 );
    "FutureMonth";
    "PastOrCurrent"
)

    This formula is comparing End of Month based on the Date column and current month.

     

    Should give you expected result:

     

    Regards,

    MFelix

     

  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi RUION,

     

    The formula from MFelix is Dax expression which is used to create the calculated column.

     

    You could paste the formula when you create a caluclated column in Data view.

     

     

    For further , we could take a look at the article below: Calculated columns in Power BI Desktop , which explains the main difference between the two columns.

     

    Best  Regards,

    Cherry

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi RUION,

     

    Why are you comparing if YEAR is greater than this one OR MONTH is greater than today, for me this sintax doesn't make sense because you need to check if the all date is greater than today right?

     

    How is your LoadgDate column looks like in terms of data?

    Do you want to only consider as future months after the end of current month or current date?

     

    Regards,

    MFelix

    • RUION's avatar
      RUION
      Frequent Visitor
      8 years ago

      Hi MFelix, 

       

      I don't want to compare with current date because ALL current month's days should be tagged as "PastOrCurrent".  Future month starts at the end of current month.  

      I hope the screenshot answers your question about how the column looks like.  If you were expecting something else, please let me know.  I am new to PowerBi... 

       

      Thanks in advance.  

       

        

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

        Hi RUION,

         

        Try to simplify your formula using this:

         

        Future/Past =
IF (
    EOMONTH ( Table1[Date]; 0 ) > EOMONTH ( TODAY (); 0 );
    "FutureMonth";
    "PastOrCurrent"
)

        This formula is comparing End of Month based on the Date column and current month.

         

        Should give you expected result:

         

        Regards,

        MFelix

         

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I'd suggest one of the few:
    1) in the existing formula - switch from OR to AND and from > to >= for YEAR (to cover current year)
    2) if you have actual date type field - use EOMONTH to get the last day of the current month, and compare the dates
    https://msdn.microsoft.com/en-us/query-bi/dax/eomonth-function-dax
    3) in case there is no date type field in your data - you can compare numbers in the format YYYYMM e.g. 201806 (so Year * 100 + Month). With this approach you have single criteria to compare