Need help getting an active count measure

[RichOB]

Hi, I posted something similar recently and need to know how to make some slight changes for a different result. I need to know the number of Counties that are active in each quarter. Using the table below, the 2024/2025 financial quarters should be:

Q1 - 3

Q2 - 3

Q3 - 2 

Q4 - 2

Counties	Property	Created_date	Decommissioned_Date
Edinburgh	L1	01/01/2022
Edinburgh	L2	01/01/2022	05/04/2024
Edinburgh	L3	01/01/2022
Glasgow	L4	01/01/2022	01/09/2024
Glasgow	L5	01/01/2022	01/09/2024
Glasgow	L6	01/08/2024	01/09/2024
Glasgow	L7	01/01/2022	01/09/2024
Glasgow	L8	01/01/2022	01/09/2024
Manchester	L9	01/01/2022
Manchester	L10	01/10/2024	01/02/2025

If a Decommissioned_Date is blank, that means the Property and County is active.

As L4-L8 (Glasgow) was decommissioned on 01/09/2024, it was technically active in July and August and therefore needs to be counted in Q2.

Thanks

[v-mdharahman]

Hi @RichOB,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are trying to get the data of all the active counties in a quarter. As @grazitti_sapna and @mark_endicott both have responded to your query, kindly go through their response and check if it answers your query.

I would also take a moment to thank @grazitti_sapna and @mark_endicott, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[grazitti_sapna]

Hi @RichOB,

To calculate the number of active Counties per quarter (based on active properties within each County), we need to evaluate whether each property was active at any point during the quarter, then count distinct Counties per quarter.

We need to create a quarte table, use below DAX

QuarterPeriods =
DATATABLE (
"Quarter", STRING,
"StartDate", DATE,
"EndDate", DATE,
{
{"Q1", DATE(2024,4,1), DATE(2024,6,30)},
{"Q2", DATE(2024,7,1), DATE(2024,9,30)},
{"Q3", DATE(2024,10,1), DATE(2024,12,31)},
{"Q4", DATE(2025,1,1), DATE(2025,3,31)}
}
)

Now create a measure to count active Counties, Use below DAX

Active Counties =
VAR SelectedStart = SELECTEDVALUE(QuarterPeriods[StartDate])
VAR SelectedEnd = SELECTEDVALUE(QuarterPeriods[EndDate])

RETURN
CALCULATE(
DISTINCTCOUNT(Properties[Counties]),
FILTER(
Properties,
Properties[Created_date] <= SelectedEnd &&
(
ISBLANK(Properties[Decommissioned_Date]) ||
Properties[Decommissioned_Date] > SelectedStart
)
)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[RichOB]

Hi @grazitti_sapna thanks for your reply! Does this mean that if I want to add 2025 data I would need to add 4 more quarter dates to the first measure? I should have said I'm working with live data and already have a calendar, if that matters? Here's my calendar:

[grazitti_sapna · Accepted solution]

Hi @RichOB,

You can add a YearQuarter column to your existing Calendar table like below

YearQuarter =
'Calendar'[Year] & " Q" & 'Calendar'[Quarter]

And then update your measure as 

Active Counties =
CALCULATE (
DISTINCTCOUNT ( Properties[County] ),
FILTER (
Properties,
Properties[Created_date] <= MAX ( 'Calendar'[Date] ) &&
(
ISBLANK ( Properties[Decommissioned_Date] ) ||
Properties[Decommissioned_Date] > MIN ( 'Calendar'[Date] )
)
)
)

This way you don't have to add table/column manually and it'll be updated dynamically.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[RichOB]

Thanks so much this is fantastic!

[mark_endicott]

@RichOB - If you'd like an answer to this, more information is necessary. 

Let's start with:

1) was your last question answered? 

2) if yes, can you post the link?

3) do you have a date table that defines the Quarters?

4) does this date table have a relationship to the table with your counties and decomissioned dates?

5) what DAX have you tried so far?