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

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

 

NamePropertyCreated_date Decommissioned_Date
EdinburghL101/01/2022 
EdinburghL201/01/202205/04/2024
EdinburghL301/01/2022 
Glasgow L401/01/2022 
Glasgow L501/01/2022 
Glasgow L601/08/202401/11/2024
Glasgow L701/01/202210/11/2024
Glasgow L801/01/2022 
ManchesterL901/01/2022 
ManchesterL1001/10/202401/02/2025

 

Thanks!

 

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.

4 Replies

  • 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

  • govind_021's avatar
    govind_021
    Icon for Super User rankSuper User
    1 year ago

    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

  • 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