Get measured value at a given time

[AlexisCohen88K]

Hello,

I'm trying to calculate the number of Houses which was "For sale" at a given time.

My Columns are : House ID, Date to Market, Date Sold.

The result expected would be something like a table with the following columns : Month Year, Qty For sale

The final goal is to plot a chart showing the evolution of records for sale through time (21 houses were for sale in march 2020, 19 in May etc...)

I really don't know where to start from. I don't even know wether DAX or M is more adapted.

Would you have some leads?

Many thanks!

[amitchandak]

@AlexisCohen88K , Not very clear. You can create a date table and join with your date. There you can have month and year and plot count of house id against that

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[AlexisCohen88K]

Hi @amitchandak .

Many thanks for your answer.

To which date column am I supposed to link the Date table? I can't see how this can work

To be clearer : I  have an "Opened Date" and a "Closed date" column. I want to know the number of records opened at a given time, preferably with a monthly increment.

[v-easonf-msft]

Hi， @AlexisCohen88K 

Not very clear.

If possible,can you share you sample pbix file or  data for testing.

I make a sample file ,hope it can work for you.Best Regards,

Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisCohen88K · Accepted solution]

Hello,

Many thanks for your help.

I managed to find the solution by creating a date table in power M using the following tutorial

Then, I added my table and created a custom column that check for each date if the record is for sale or not.

Thanks again everyone for your help and time!