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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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