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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
andreaaquilina
Frequent Visitor

IF and AND measure with Dates

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.

andreaaquilina_1-1659684954882.png

 

Thanks in advance!

1 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

OliT_0-1660038806748.png

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

OliT_1-1660039025956.png

then create a count measure

 

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

 

OliT_2-1660039135435.png

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

View solution in original post

4 REPLIES 4
selimovd
Super User
Super User

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

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.

andreaaquilina_0-1659687799277.png

I hope I managed to explain my self better.

 

Best regards,

 

Andrea

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

OliT_0-1660038806748.png

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

OliT_1-1660039025956.png

then create a count measure

 

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

 

OliT_2-1660039135435.png

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

Thank you OliT, This helped me resolve my issue.

 

Best regards,

 

Andrea

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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