Measure to count totals based on a date

[RichOB]

Hi, when the decommissioned date is empty, the property is open. Using the table below, I need to show the property count 2 ways:

The total number of active properties in a quarter:

2024 Q1 - 8

2024 Q2 - 8 (as L6 was created in Q2)

2024 Q3 - 9 (as L10 was created in Q3)

2024 Q4 - 7

At the end of the quarter, how many were remaining

2024 Q1 - 1 was decommissioned so 5 remaining

2024 Q2 - 8 

2024 Q3 - 7 as L6 + L7 as was decommissioned in Q3

2024 Q4 - 6 as L10 was decommissioned in Q4

Name	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
Glasgow	L5	01/01/2022
Glasgow	L6	01/08/2024	01/11/2024
Glasgow	L7	01/01/2022	10/11/2024
Glasgow	L8	01/01/2022
Manchester	L9	01/01/2022
Manchester	L10	01/10/2024	01/02/2025

Thanks!

[v-tejrama]

Hi @RichOB ,

Thanks for reaching out to the Microsoft fabric community forum.

I faced a similar challenge and was able to resolve it by creating a DAX measure that calculates the number of active properties by quarter.

In my scenario, a property is considered active if it was created on or before the selected date and has either not been decommissioned or was decommissioned after the selected date.

To achieve this, I used a DateTable with a relationship to the Created_date column in the Properties table.

Here is the DAX :

ActiveProperties =
CALCULATE(
COUNTROWS(Properties),
FILTER(
ALL(Properties),
Properties[Created_date] <= MAX('DateTable'[Date]) &&
(
ISBLANK(Properties[Decommissioned_date]) ||
Properties[Decommissioned_date] > MAX('DateTable'[Date])
)
)
)

This measure, when plotted on a line chart with DateTable[YearQuarter] on the X-axis, accurately reflects how many properties were active during each quarter.

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[govind_021]

Hey @RichOB 
try this maeasure

Remaining Properties at Quarter End =
VAR EndDate = MAX('DateTable'[Date])
RETURN
CALCULATE(
COUNTROWS(Properties),
FILTER(
Properties,
Properties[Created_date] <= EndDate &&
(ISBLANK(Properties[Decommissioned_Date]) || Properties[Decommissioned_Date] > EndDate)
)
)


Best Regards
Govind Sapkade ( Microsoft Certified Data Analyst , PL 300 Certified , MS Fabric Enthusiast)
Let's Connect
Linkdin - www.linkedin.com/in/govind-sapkade-845104225
Youtube - http://www.youtube.com/@govind_dataanalyst

[bhanu_gautam · Accepted solution]

@RichOB , First create a date table

Then, Create a measure to count active properties in a quarter:

ActiveProperties =
CALCULATE(
COUNTROWS('Table'),
'Table'[Created_date] <= MAX('DateTable'[Date]),
OR(
ISBLANK('Table'[Decommissioned_Date]),
'Table'[Decommissioned_Date] > MAX('DateTable'[Date])
)
)

Create a measure to count remaining properties at the end of a quarter:

DAX
RemainingProperties =
CALCULATE(
COUNTROWS('Table'),
'Table'[Created_date] <= MAX('DateTable'[Date]),
OR(
ISBLANK('Table'[Decommissioned_Date]),
'Table'[Decommissioned_Date] > MAX('DateTable'[Date])
),
'Table'[Decommissioned_Date] > MIN('DateTable'[Date])
)

Use a line chart or bar chart to visualize the measures over time.
Drag the Date Table's Quarter column to the axis and the measures to the values.

[RichOB]

Hi @bhanu_gautam I wonder if you could help with a further question with this, please?

As long as one property is active in a quarter then the Name is considered active for that quarter. How can I show that in a countplease? So Q1-Q4 2024 would show as 3 per quarter please? as Edinburgh, Glasgow and Manchester had at least a live property, and obviously say if Manchester had a decommissioned date on all properties in a quarter, then the total would be 2 etc.

Thanks