Flag between months

[DipeshVadgama]

Basically I want to check if month of given dates by user is equal or less or greater than current month.

If its equal then " Current month Due", If its less then "Overdue", If its greater then "Ontime" .

When I apply simple formula month(Date) its does work, but it also give me wrong flag as its do not compare with years.

Please suggest.

    If([Date_Field]= 'Closed','Closed',
    If([Date_Field]= 'Cancelled','Cancelled',
    If([Date_Field]= 'TBC','TBC',
    If([Date_Field]= '','Blank',
    If(Month([Date_Field])= Month(Today()),'Current Month Due',
    If(Month([Date_Field])> Month(Today()),'Ontime',
    If(Month([Date_Field])< Month(Today()),'Overdue'))))))) AS Flag

[Mauritz_SA]

Hi Dipesh

Try this:

[Sample]:
LOAD
[Dates] AS [Dates],
If([Dates]= 'Closed','Closed',
If([Dates]= 'Cancelled','Cancelled',
If([Dates]= 'TBC','TBC',
If([Dates]= '','Blank',
If(Year([Dates])>Year(Today()),'Ontime',
If(Year([Dates])<Year(Today()),'Overdue',
If(Month([Dates])= Month(Today()),'Current Month Due',
If(Month([Dates])> Month(Today()),'Ontime',
If(Month([Dates])< Month(Today()),'Overdue'))))))))) AS Flag
FROM [lib://Downloads/Sample.xlsx]
(ooxml, embedded labels, table is Sample);

Or this if you just want to return the column name when it is not a date (or 'Blank' when it is empty):

[Sample]:
LOAD
[Dates] AS [Dates],
If([Dates]= '','Blank',
If(Year([Dates])>Year(Today()),'Ontime',
If(Year([Dates])<Year(Today()),'Overdue',
If(Month([Dates])= Month(Today()),'Current Month Due',
If(Month([Dates])> Month(Today()),'Ontime',
If(Month([Dates])< Month(Today()),'Overdue',[Dates])))))) AS Flag
FROM [lib://Downloads/Sample.xlsx]
(ooxml, embedded labels, table is Sample);

It basically checks for the year first and only compares the months if the year of the date is the same as the current year.

Regards,

Mauritz