Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ToddMate
Helper II
Helper II

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

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.


Annotation 2019-09-11 110825.png

 

Thanks in advance,
Todd

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this calculated column formula

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Try this calculated column formula

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

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

amitchandak
Super User
Super User

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.