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]
[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]