counting open records for the past 12 months

[boyddt_mn]

I have been working on this for a week now and one of my colleagues has been at it for about a month with no luck. Our requirement is to display, over a 12 month period, the number of non-aged documents for each month. The rules are outlined below. I came across the tickets discussion (https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147) and I had issues implementing it due to the age requirements. It will help when I get to the rate measurement since it seems that it will tell me how many open documents there are. The big take-away is that if a document was closed during a particular month, it doesn't get counted, regardless of how old it was. I've included as much information that I could conjure at this late hour.

environment: Power BI & DAX

Requirement: report previous 12 months of the rate of non-aged NR's using either a line graph or column chart

Rules Aged NRs:
for each reporting month, IF 'nc main'[nc completed date] is blank && 'nc main'[nc created date] <= eomonth(reporting period) && eomonth(reporting period) - 'nc main'[nc created date] > 90
for each reporting month, IF 'nc main'[nc completed date] is not blank && 'nc main'[nc created date] <= eomonth(reporting period) && 'nc main'[nc completed date] > eomonth(reporting period) && eomonth(reporting period) - 'nc main'[nc created date] > 90
for each reporting month, IF 'nc main'[nc completed date] is not blank && 'nc main'[nc created date] <= eomonth(reporting period) && 'nc main'[nc completed date] <= eomonth(reporting period)

Rules for Not Aged
for each reporting month, IF 'nc main'[nc completed date] is blank && 'nc main'[nc created date] <= eomonth(reporting period) && eomonth(reporting period) - 'nc main'[nc created date] < 90
for each reporting month, IF 'nc main'[nc completed date] is not blank && 'nc main'[nc created date] <= eomonth(reporting period) && 'nc main'[nc completed date] > eomonth(reporting period) && eomonth(reporting period) - 'nc main'[nc created date] < 90

Rules for All Open NRs:
for each reporting month, IF 'nc main'[nc completed date] is blank for eomonth(reporting period) || 'nc main'[nc completed date] > eomonth(reporting period)

Tables & Columns
calendar table: 'datekey'
primary column: [date]

data table: 'nc main'
primary columns:
[nc created date]
[nc completed date]

Examples:
Document Number Is Aged Days Open Last Reporting Date NC Created Date NC Completed Date Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23
NRDOC-13720 1 124 2023-04 1/4/2023 5/8/2023 -- -- -- 116 -- -- -- -- -- --
NRDOC-13729 0 89 2023-03 1/5/2023 11/25/2023 -- -- -- 115 146 176 207 238 268 299
NRDOC-13738 0 66 2023-02 1/6/2023 3/13/2023 -- -- -- -- -- -- -- -- -- --
NRDOC-13879 1 202 2023-07 1/18/2023 8/8/2023 -- -- -- 102 133 163 194 -- -- --

April has 3 aged
May, June, & Jul have 2 aged for each month
Aug, Sep, & Oct have 1 aged for each month

Relationships:
there is an active Relationship between 'datekey'[date] and 'nc main'[nc created date]
there is an inactive Relationship between 'datekey'[date] and 'nc main'[nc completed date]

The result can either be measures, calculated column or a mixture of both

[DataNinja777]

Hi @boyddt_mn ,

To solve the problem of calculating the number of non-aged documents over a 12-month period, you can use Power BI and DAX by defining measures that follow the rules for aged and non-aged NRs. The relationships in the data model are critical. Maintain an active relationship between 'datekey'[date] and 'nc main'[nc created date] while utilizing the inactive relationship between 'datekey'[date] and 'nc main'[nc completed date] with the USERELATIONSHIP function as needed.

First, define the measure for aged NRs. This measure filters the data based on the rules provided. It checks whether the documents meet the following conditions: the nc completed date is blank, the nc created date is on or before the end of the reporting period, and the difference between the nc created date and the end of the reporting period is greater than 90 days. Alternatively, it accounts for documents with non-blank nc completed date values, ensuring that the completion date is either after or on the reporting period while meeting the aging criteria. The measure can be written as follows:

Aged NRs = 
VAR ReportingPeriod = MAX('datekey'[date])
RETURN
    COUNTROWS(
        FILTER(
            'nc main',
            ('nc main'[nc completed date] = BLANK() && 
             'nc main'[nc created date] <= EOMONTH(ReportingPeriod, 0) &&
             DATEDIFF('nc main'[nc created date], EOMONTH(ReportingPeriod, 0), DAY) > 90)
            ||
            ('nc main'[nc completed date] > EOMONTH(ReportingPeriod, 0) &&
             'nc main'[nc created date] <= EOMONTH(ReportingPeriod, 0) &&
             DATEDIFF('nc main'[nc created date], EOMONTH(ReportingPeriod, 0), DAY) > 90)
            ||
            ('nc main'[nc completed date] <= EOMONTH(ReportingPeriod, 0) &&
             'nc main'[nc created date] <= EOMONTH(ReportingPeriod, 0))
        )
    )

The measure for non-aged NRs similarly filters the data to count documents where the nc completed date is blank or after the reporting period, provided that the difference between the nc created date and the end of the reporting period is less than 90 days. It can be written as follows:

Not Aged NRs = 
VAR ReportingPeriod = MAX('datekey'[date])
RETURN
    COUNTROWS(
        FILTER(
            'nc main',
            ('nc main'[nc completed date] = BLANK() &&
             'nc main'[nc created date] <= EOMONTH(ReportingPeriod, 0) &&
             DATEDIFF('nc main'[nc created date], EOMONTH(ReportingPeriod, 0), DAY) < 90)
            ||
            ('nc main'[nc completed date] > EOMONTH(ReportingPeriod, 0) &&
             'nc main'[nc created date] <= EOMONTH(ReportingPeriod, 0) &&
             DATEDIFF('nc main'[nc created date], EOMONTH(ReportingPeriod, 0), DAY) < 90)
        )
    )

To calculate all open NRs, create a measure that includes documents without a completion date or with a completion date after the reporting period:

All Open NRs = 
VAR ReportingPeriod = MAX('datekey'[date])
RETURN
    COUNTROWS(
        FILTER(
            'nc main',
            'nc main'[nc completed date] = BLANK() || 
            'nc main'[nc completed date] > EOMONTH(ReportingPeriod, 0)
        )
    )

These measures can be visualized using a line graph or column chart with 'datekey'[date] on the X-axis to represent the reporting period and the measures as the Y-axis values. Apply a slicer or filter to display data for the last 12 months.

If any of these measures require data from the inactive relationship between 'datekey'[date] and 'nc main'[nc completed date], use the USERELATIONSHIP function within the measures. For example:

Completed NRs = 
CALCULATE(
    [Your Measure Logic],
    USERELATIONSHIP('datekey'[date], 'nc main'[nc completed date])
)

Finally, validate the measures against your example data to ensure correctness. By implementing these steps, you can accurately display the number of non-aged NRs over a 12-month period while adhering to your outlined rules. Let me know if further refinements are needed.

Best regards,

[boyddt_mn]

Thank you for your quick response. This has gotten me further along but I'm seeing two issues. The "All Open NRs" and "Not Aged NRs" produce the same numbers. Then The "Aged NRs" measure for November 2024 shows a value greater than the number of open NRs. I'm including the PBIX file.

https://www.dropbox.com/scl/fi/qp9f9t5lws2jp1g2lmo2j/Document_Age.pbix?rlkey=00p1m61kotgvck82fevksq6...

[DataNinja777 · Accepted solution]

Hi @boyddt_mn ,

Here are the updated formulas for each measure, ensuring proper filtering logic and alignment between "All Open NRs," "Aged NRs," and "Not Aged NRs":

All Open NRs = 
VAR ReportingPeriod = MAX('datekey'[date])
RETURN
    COUNTROWS(
        FILTER(
            'nc main',
            'nc main'[nc completed date] = BLANK() || 
            'nc main'[nc completed date] > EOMONTH(ReportingPeriod, 0)
        )
    )

This formula counts all rows where the nc completed date is either blank or falls after the end of the reporting period, effectively identifying all open records.

Aged NRs = 
VAR ReportingPeriod = MAX('datekey'[date])
RETURN
    COUNTROWS(
        FILTER(
            'nc main',
            (
                ('nc main'[nc completed date] = BLANK() || 
                 'nc main'[nc completed date] > EOMONTH(ReportingPeriod, 0)) &&
                'nc main'[nc created date] <= EOMONTH(ReportingPeriod, 0) &&
                DATEDIFF('nc main'[nc created date], EOMONTH(ReportingPeriod, 0), DAY) > 90
            )
        )
    )

This formula ensures that only rows meeting the "open" criteria are considered aged, specifically those where the nc created date is before or equal to the reporting period and the age is greater than 90 days.

Not Aged NRs = 
[All Open NRs] - [Aged NRs]

This formula ensures that "Not Aged NRs" are derived as the difference between all open records and aged records, relying on the assumption that "Aged NRs" is a proper subset of "All Open NRs."

Validation

To ensure correctness:

1. Validate that Aged NRs and Not Aged NRs add up to All Open NRs:

Validation = [Aged NRs] + [Not Aged NRs]

• The result of this measure should equal All Open NRs.
• Debug intermediate logic by visualizing data for November 2024, including fields like nc created date, nc completed date, and calculated age:

Age = DATEDIFF('nc main'[nc created date], MAX('datekey'[date]), DAY)

By aligning the filtering logic and verifying results with intermediate measures and visualizations, these formulas should resolve the discrepancies observed.

Best regards,