cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ToddMate Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User IV
Super User IV

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

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
Super User IV
Super User IV

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

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

 

 





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Super User IV
Super User IV

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

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

ToddMate Regular Visitor
Regular Visitor

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

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

Super User IV
Super User IV

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

You are welcome.


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

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

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 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors