March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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!
Solved! Go to Solution.
>> 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
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.
I hope I managed to explain my self better.
Best regards,
Andrea
>> 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
Thank you OliT, This helped me resolve my issue.
Best regards,
Andrea
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
85 | |
79 | |
64 | |
59 |
User | Count |
---|---|
140 | |
122 | |
105 | |
94 | |
90 |