Forum Discussion
Post PartisanNeed help getting an active count measure
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!
Super UserHi 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!- RichOB
Thanks so much this is fantastic!