If statement based on date of the month for the start and the end of the service.

[ToddMate]

Hi,

I have what i assume is a simple statement but i am unable to build such statment to update a column.

What i need is to say:

If "DateStart" = 1st of the month and If "DateEnd" = 1st of the month then show "Full Month" otherwise "Pro Rata"

The table name is "Invoices". A sample of the table is below. Can anyone help me with this statement.

Thanks in advance,
Todd

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=IF(AND(DAY(Invoices[StartDate])=1,DAY(Invoices[EndDate])=1),"Full Month","Pro Rata")

Hope this helps.

[Ashish_Mathur]

You are welcome.

[ToddMate]

Hi,

I have this statement below that works brilliantly, however i need to extend this now in that if the result is false i would like to do another Nested IF statement. The current statement is:

Full Month or Pro Rata = IF(AND(DAY('Invoices'[Charge From])=1,DAY('Invoices'[Charge To])=1),"Full Month","Pro Rata")

What i would like is to extend this rather than closing it off after a false result (Pro Rata). I would like to extend this to say:

IF 'Invoices' [Charge From] is NULL then "One Off" otherwise "Pro Rata"

Can anyone help me.

Thanks in advance.
Todd

@Ashish_Mathur 

[ToddMate]

Thank you @Ashish_Mathur , worked perfectly .. thank you!

[amitchandak]

Try like this. It is a new column not measure

Column = if(AND(Sales[Order_Date] = STARTOFMONTH('Order'[Order Date]), Sales[Requested_Date].[Date] = STARTOFMONTH('Order'[Order Date])),Sales[Sales],Sales[COGS])