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!
Post PartisanHi 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:
Super UserHi 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!