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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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