Need help creating an aging measure

[ndbwhunter]

Hello All! For starters, I am very new to Power BI so please go easy on me. I'm trying to create an aging measure to be used in a visual that will count the total number of claims pending over 31 days at the start of each month. This will be used as a historical visual so most of the figures will remain the same. The only numbers that may change will be the current and previous months.

The formula will be using the claim's notification date, decision date, and the start of month date. If the decision date is null, the formula needs to subtract the notification date from the first day of the month to determine if the claim has been pending for more than 31 days. This claim will only be counted in the total if it exceeds 31 days from the start of the month. Once it has exceeded the 31 day mark, the claim will continue to show up in each month's count unless the start of month date exceeds the decision date. The example below was done in Excel, but unfortunately I cant get the formula to work there either. Notification Date Decision Date 12/27/2016 4/13/2017 1/1/2017 0 2/1/2017 1 3/1/2017 1 4/1/2017 1 5/1/2017 0 <colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <tbody> </tbody>

<colgroup><col width="1045" style="width: 784pt; mso-width-source: userset; mso-width-alt: 38217;"> <tbody> </tbody>

 

[Ozeroth]

Hello ndbwhunter,

What you're wanting is basically an 'events in progress' measure, where you are counting claims that are somewhere between Notification Date + 31 and Decision Date as at the start of a given monthly period.

One way of doing this without restructuring your Claims data is following a pattern from this paper. I modified the pattern on page 27.
https://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

I created a sample PBIX here using your sample claim plus one other.
The model contains a Claims table and a disconnected Date table.

The measure I created is below.

• First it aggregates rows of the Claims table, resulting in a table of Notification Dates & Decision Dates with a Claim count (ClaimsSummarized variable).
• It then filters this table down to the rows corresponding to claims for which the period from Notification Date + 31 to Decision Date includes the minimum date filtered (FilteredToMinDate variable).
• Finally, it counts the Claims.
• Note: The exact boundary conditions might need to be checked

Claims Pending More Than 31 Days as at Start of Period = 
VAR Threshold = 31
VAR MinDate =
    FIRSTDATE ( 'Date'[Date] )
VAR ClaimsSummarized =
    ADDCOLUMNS (
        SUMMARIZE ( Claims, Claims[Notification Date], Claims[Decision Date] ),
        "Claims", CALCULATE ( COUNTROWS ( Claims ) )
    )
VAR FilteredToMinDate =
    GENERATE (
        ClaimsSummarized,
        INTERSECT (
            MinDate,
            DATESBETWEEN (
                'Date'[Date],
                Claims[Notification Date] + Threshold,
                Claims[Decision Date]
            )
        )
    )
VAR ClaimCount =
    SUMX ( FilteredToMinDate, [Claims] )
RETURN
    ClaimCount

There are certainly other ways to write this measure (see paper above for other options) and another option involves restructuring the data so that you have one row per date on which a Claim is active (see this article).

Regards,
Owen