cancel
Showing results for
Did you mean:
Highlighted
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.

Todd

1 ACCEPTED SOLUTION

Accepted Solutions
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
5 REPLIES 5
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

## 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
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

## 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
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.

Todd

@Ashish_Mathur

Announcements

#### 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!

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?

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

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

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors