Finding number of active sites based on date

[Anonymous]

I have a table with data concercing sites (physical locations) and status changes in which sitestat_id 'A' stands for active, sample:

site_no	sitestat_id	change_date
661740	A	3-8-2020 11:21:00
661740	C	3-7-2020 11:19:00
24204	A	3-7-2020 11:14:00
24204	C	31-6-2020 15:16:00
8809	A	31-6-2020 13:18:00
8809	C	23-5-2020 12:43:00
663577	A	21-5-2020 09:29:00
48768	R	19-5-2020 15:16:00

I also have a (currently disconnected) date table.

I'm looking for a way to write a measure which shows the number / count of active (A) sites per month in a table, based on the site status on the last day of the month.

I also would like a measure whichs works based on a date slicer.

Open to other solutions than measures as well.

[Anonymous]

Thank you for your replies guys 🙂

@harshnathani,

That doesn't yield the exact result I'm looking for. I think it counts the number of new active sites while I'm also looking for already active sites. E.g. the count for September 2019 should also list sites that became active in August and July.

Also forgot to mention I meant month + year instead of just month.

@amitchandak,

That does seem like something that should work but it currently doesn't. MAXX() needs two arguments. I would like to adjust but I can't wrap my head around it yet. What do St and Ed do?

[Anonymous]

@Anonymous 

Try add DATESMTD in the measure:

Measure = CALCULATE(COUNT('Table'[site_no]), FILTER('Table',[sitestat_id] = "A"),DATESMTD('Date'[Date]))

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

[Anonymous]

@Anonymous 
@amitchandak 
@harshnathani 

I'm looking for something cumulative, but it shouldn't count it if the value closest (but before) to the desired date isn't "A". I know it should be possible but it really has me stumped.

[amitchandak]

@Anonymous , Try like

countx(filter(summarize(Table, table[site_no], "St", calculate(Min(Table[change_date]),table[sitestat_id]="A"), "Ed", calculate(Min(Table[change_date]),table[sitestat_id]="C")
) ,[St] <= maxx(Date[DAte]) && [Ed]>= maxx(Date[DAte])),[site_no])

Assumed Date is disconnected table

[harshnathani]

Hi @Anonymous ,

Create a month Column

_Month = MONTH(change_date)

Then create a measure

_CountM  = CALCULATE(COUNT('Table'[site_no]), site_stat = "A")

Regards,

Harsh Nathani