DAX Measure for counting per month of a year from a date entered slicer

[KarenKazab]

Hi all,

Need some help with a measure, can't quite work out how to tackle it

I have a date table with no relationship to the main fact table of void and let properties, the user uses a date slicer (from the date table) to choose one date and the below measure uses this date entered to work out on that date what was vacant at that point in time. I need to be able to work out using the same date entered through the slicer what would have been the vacant stock at each month prior to the date entered up to a year i.e. if entered today (3rd August 2021) then what was vacant at 3rd July/3rd June/3rd May and so on up to a year. I could do a measure for each month seperately but then how could that display easily on a line graph trend for a year?

Measure for vacant stock count at point in time entered via date slicer:

Count Vacant Stock =

VAR vEnd = MAX('Date'[Date])

RETURN

CALCULATE(

COUNT('Occupied Vacant Stock At Date'[Tenancy Number]),

FILTER ('Occupied Vacant Stock At Date',

'Occupied Vacant Stock At Date'[TenancyCommencementDate] <= vEnd &&

('Occupied Vacant Stock At Date'[TenancyTerminationNoNulls] >= vEnd &&

'Occupied Vacant Stock At Date'[DisposedDateNoNulls] >vEnd &&

'Occupied Vacant Stock At Date'[PropertyHandoverDate2] <=vEnd &&

NOT(ISBLANK('Occupied Vacant Stock At Date'[PropertyHandoverDate2]))

&& 'Occupied Vacant Stock At Date'[Occupied Status] ="Vacant"

)

))

[KarenKazab]

Hi thanks that helps a bit in order to get the range of dates available but I need to be able to count for each month, i.e. a property may be vacant across multiple months, so at one point of each month depending on which day they enter i.e. the end of the month it would need to calculate for each end of month for the following year.

Here is what the data would look like, so checking three date columns to see if they were vacant across each month of the year based upon the date slicer the user enters. So I would then have a line chart to show the count of vacancies and a month/year on the vertical axis to show a trend of vacant properties across the months of the year. Below based upon if the user entered the 30/05/2021 (so the range would be 30/05/2020 - 30/05/2021)

Seq	Address	Property Handover Date	Void Start Date	Void End Date	Dec 2020 Vacant Count	Jan 2021 Vacant Count	Feb	March	April	May
1	Add 1	30/01/2021	30/01/2021	01/06/2021	0	1	1	1	1	1
2	Add 2	27/02/2021	27/02/2021	01/03/2021	0	0	1	0	0	0
3	Add 3	30/01/2021	30/01/2021	01/03/2021	0	1	1	0	0	0
4	Add 4	20/06/2021	30/06/2021	20/08/2021	0	0	0	0	0	0
5	Add 5	30/01/2020	30/01/2020	30/03/2020	0	0	0	0	0	0
6	Add 6	20/08/2020	20/01/2020	30/08/2021	1	1	1	1	1	1
				Total Vacant Per Month	1	3	4	2	2	2

[v-jayw-msft]

Hi @KarenKazab ,

Thanks for the data. I'm a little confused about the data you've provided above. Is it a table or visual?

Best Regards,

Jay

[KarenKazab]

Its a bit of both, what the data looks like and then what I would expect the count to be for each row to display what would be expected. 

I am pretty much there with the DAX now, except I have had to do month end counts which is ok not exactly what I wanted to achieve but it'll do, the only thing I need to add is another filter on the date table based upon the commented out var filterdate but not sure where to add that.

Count Vacant Stock Year 3 date =

VAR vEnd = MAX('Date'[Date])

var _firstStartDate = date(year(vEnd)-1,month(vEnd),day(vEnd))

//var filterdate =

//FILTER('Date', 'Date'[Date] >=_firstStartDate && 'Date'[Date] <=vEnd)

var _currDate=SELECTEDVALUE('Date'[EndOfMonth])

var _vacantprops=

FILTER(

'Occupied Vacant Stock At Date',

'Occupied Vacant Stock At Date'[TenancyCommencementDate] <=_currDate &&

'Occupied Vacant Stock At Date'[TenancyTerminationNoNulls] >=_currDate &&

'Occupied Vacant Stock At Date'[DisposedDateNoNulls] >_currDate &&

'Occupied Vacant Stock At Date'[PropertyHandoverDate2] <=_currDate &&

NOT(ISBLANK('Occupied Vacant Stock At Date'[PropertyHandoverDate2])) &&

'Occupied Vacant Stock At Date'[Occupied Status] ="Vacant"

)

var _countRows=COUNTROWS(_vacantprops)

return

if(_currDate <=TODAY() && _currDate >=_firstStartDate && _currDate <=vEnd,

if(ISBLANK(_countRows),0,_countRows)

)

[v-jayw-msft]

Hi @KarenKazab ,

Please show some sample data and expected result to us if you need DAX help.

You could check the topic about How-to-Get-Your-Question-Answered-Quickly .

Best Regards,

Jay

[amitchandak]

@KarenKazab , Use these three

VAR vEnd = MAXX(allselected('Date'),'Date' [Date])
VAR vStart = date(year(vEnd)-1,month(vEnd),day(vEnd))
VAR vDay = day(vEnd)

Check for range between vStart  and vEnd end check Day of date = vDay (this will insure same day of each month

)

Like 3rd july, 3rd June etc