Forum Discussion
Post PartisanNeed help getting an active count measure
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
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!
6 Replies
- mark_endicott
Super User
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?
- 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!
RichOBHi 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
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!
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
