count number of items between 2 different date columns

[zubair242]

Hi everyone,

I have an interesting scenario where I want to know whether property/asset is in the market or not. I have Date sold column which shows when it is sold. 

Now, I want to calculate available properties on a particular day. For example, there are 2 properties on 22nd, 4 on 23rd( 2 new and 2 previous), 3 on 24th(1 new, 2 from 23rd, but properties which were available on 22nd sold on 24th). How can I calculate using DAX. Snapshot is attached with the sample PBI file.

https://drive.google.com/file/d/170YhhUi8Ymtsygbb7Eb6mTF6qC1afTIv/view?usp=sharing

Regards,

Zubair

[rajendraongole1]

Hi @zubair242 - as per shared pbix and given inputs , on 23rd aug, we ill get available properties only 2 not 4. can you please check and let know if something is missed

Available Properties =
VAR CurrentDate = MAX('DateTable'[Date])
RETURN
CALCULATE(
COUNTROWS(
FILTER(
'YourTable',
'YourTable'[Active] = 1 &&
(
('YourTable'[Date] <= CurrentDate && ISBLANK('YourTable'[Date Sold])) ||
('YourTable'[Date] <= CurrentDate && 'YourTable'[Date Sold] > CurrentDate)
)
)
)
)

Hope this helps.

[zubair242]

Thank you @rajendraongole1 .. When I implemented this, I got the properties which are currently active. I should have 2 active on 22nd and 4 on 23rd but I got the following. The first 2 properties became inactive on 24th. Hope that makes sense?

Required output:

Current output: