Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Measure to count totals based on a date

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 ...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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.

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago

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