Need help creating an aging measure

ndbwhunter

New Member
Joined
Nov 28, 2018
Messages
2
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.

[TABLE="width: 784"]
<colgroup><col width="1045" style="width: 784pt; mso-width-source: userset; mso-width-alt: 38217;"> <tbody>[TR]
[TD="width: 1045, bgcolor: transparent"]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.

[TABLE="width: 207"]
<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>[TR]
[TD="width: 68, bgcolor: transparent"][/TD]
[TD="width: 114, bgcolor: transparent"]Notification Date[/TD]
[TD="width: 93, bgcolor: transparent"] Decision Date
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12/27/2016[/TD]
[TD="bgcolor: transparent, align: right"]4/13/2017[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/2017[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/1/2017[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3/1/2017[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/1/2017[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/1/2017[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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 :)

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top