topic Re: Distinctcount on a dynamic period of time in DAX Commands and Tips

Distinctcount on a dynamic period of time

Elofly — Fri, 11 Feb 2022 13:35:07 GMT

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!

Re: Distinctcount on a dynamic period of time

Whitewater100 — Fri, 11 Feb 2022 14:53:23 GMT

Hi Elofly:

Please find link to a hopeful solution for you.

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

Re: Distinctcount on a dynamic period of time

tamerj1 — Fri, 11 Feb 2022 17:59:21 GMT

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.

Re: Distinctcount on a dynamic period of time

Whitewater100 — Sun, 13 Feb 2022 20:00:27 GMT

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!

Re: Distinctcount on a dynamic period of time

Elofly — Mon, 14 Feb 2022 13:33:13 GMT

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!

Re: Distinctcount on a dynamic period of time

Elofly — Mon, 14 Feb 2022 13:33:43 GMT

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

Re: Distinctcount on a dynamic period of time

Whitewater100 — Mon, 14 Feb 2022 15:02:45 GMT

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

Re: Distinctcount on a dynamic period of time

Anonymous — Wed, 23 Feb 2022 06:59:40 GMT

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