IF and AND measure with Dates

[andreaaquilina]

Hi,

I need to replicate the below IF and AND function from Excel into Power BI. I have some room reservations data with a Start and an End date, and as an end result I need to know how many rooms are on rent daily. I also created a Dates Table to use my reference dates

The Excel formula is this: =IF(AND(K$1>=$A4, K$1<$B4),1,0) where K$1 would be the reference date from the Dates table, $A4 is the Start date and $B4 is the End Date.

This is my end result in Excel and would like to have the same in Power BI with column totals.

Thanks in advance!

[selimovd]

Hey @andreaaquilina ,

Power BI is not Excel, so depending what you want to do, the approach would be different.

So in order to get the best result I would prefer if you tell us more what you want to do.

Otherwise the formula would work the same in Power BI, just replace cell references (e.g. $A4) with column references (e.g. Date[StartDate]).

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[andreaaquilina]

Hey Dennis,

Thanks for your reply. My aim is to be able to tell how many rooms are on rent on a specfic day. 

The below screen shot is from Excel and the total at the bottom tells us how many rooms are on rent on each day.

I hope I managed to explain my self better.

Best regards,

Andrea

[OliT · Accepted solution]

Hi @andreaaquilina 

>> My aim is to be able to tell how many rooms are on rent on a specfic day. 

My suggestion is to create a datasource like this

then in Power BI Desktop you can create a calendar table,

then create a count measure

count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Start Date]<=MIN('calendar'[Date]) && 'Table'[End Date]>=MIN('calendar'[Date])))

this measure count rows in Table where 'Table'[Start Date]<= the date of current row && 'Table'[End Date]>=  the date of current row, also you can change "<=" to "<",  ">=" to ">" if you need.

Regards,

OliT

[andreaaquilina]

Thank you OliT, This helped me resolve my issue.

Best regards,

Andrea