IF formula for future months

[RUION]

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

)

[MFelix · Accepted solution]

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]

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

[MFelix]

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]

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

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

[RUION]

That looks easier indeed, but I am getting this message when I try to use it.. And I have no clue what I am doing wrong.. Any suggestions how to overcome this?  I have tried to replace the ; with , but then I get the second screenshot. 

,

[v-piga-msft · Accepted solution]

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