Distinctcount on a dynamic period of time

[Elofly]

Dear all,

I am trying to count the number of active properties I have during a certain period of time. (60 days).

That is to say, i want to count the number of property ID that I have from the first day of the month for the past 60 days. Count of property being those that have performed one reservation.

So I have a table with

Booking date - Property ID

I would like to calculate number of active properties in June 2020 - that is to say count from 01/04/2020 to 01/06/2020

Thanks a lot for your help!

[Whitewater100 · Accepted solution]

Hi Elofly:

Please find link to a hopeful solution for you.

https://drive.google.com/file/d/1k1zx52swBgy8FwXthweXaeowJNye1-iU/view?usp=sharing 

[Anonymous]

Hi @Elofly ,

Could you tell me if your problem has been solved? If it is, kindly Accept a helpful reply as the solution. More people will benefit from it.

Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[Whitewater100 · Accepted solution]

Hi:

I put together a file that answers your requirements...I believe. I used dummy data as none was supplied.

What's missing?

https://drive.google.com/file/d/1b0mgwnTdl6aNiPcbHdQxtLohxs4lvRmg/view?usp=sharing

[tamerj1]

Hi @Elofly 

Please calrify "Count of property being those that have performed one reservation." Do you mean al least one reservation or you mean only one reservation.

do you have a date table connected to bookingdate column?

what kind of visual are going to use? A screenshot will be of great help. 

[Elofly]

Hi Tamer,

I mean at least one reservation in the next 60 days.

I have one table with all the bookings per HotelID and one table as a calendar.

I would like to use that kind of visual to see over time and every month how many unique properties were active:

Thanks a lot for your help!

[Whitewater100 · Accepted solution]

Hi Elofly:

Please find link to a hopeful solution for you.

https://drive.google.com/file/d/1k1zx52swBgy8FwXthweXaeowJNye1-iU/view?usp=sharing 

[Whitewater100]

Hello:

That was a good question tamerj1 had. I assumed you had all unique records in your example table. Can you mention which columns you use and how you count to determine if a property is active?

I would teweaak the file I made for you if your approach is different than I imigained.

Thanks!

[Elofly]

Thanks a lot for your help! You have been very useful.