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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |