Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Green0879
Frequent Visitor

"Flag" each month between two dates

Hi everyone!

 

I need to flag each month between two dates in my table so I can make a Matrix.

 

Situation:

Insurance adjusters have open claims. If the claim started on 1/1/18 and closed on 3/31/18, then the claim was open in Jan, Feb, and Mar, but not in April. I'd want that adjuster's row on the matrix to look something like this:

 

Number of Open Claims per Month: 

adjusternameJanFebMarApril
JaneDoe1110

 

That example the adjuster has only a single claim, but in reality adjusters will have hundreds of open claims.

 

The claim is a single record in the table with an open date and a close date and should be counted each month it is open. There are of course cases where the claim isn't closed yet, in those cases I use the last day of the previous month using logic, got that worked out. The dataset is always valued last day of previous month by the way.

 

In many cases claims are open several years. In the matrix, I'd only select the current calendar year for display, maybe last two calendar years, but I'd like to consider the entrie dataset so Management can view ranges.

 

How in the world would I get this done? Maybe it's simple and I'm overthinking it? I suspect another table may be used, and I can use Claim Number (unique) as an indexing key between the two tables.

 

Thanks so much for reading!

 

Kyle

1 ACCEPTED SOLUTION

Hi,

 

Try this link - https://drive.google.com/open?id=1dH-QEuEKH27RxBZlg2SoHfX5JL1zT1Cd

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

 

I am inteested in helping.  Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I appreciate your willingness!

 

Please use this dropbox link for the sample file (I used Excel for the sample file, hope that is OK). The field "Loss Date" is the start date and the field "altClose Date" is the ending date. Also, if Status is "Final", then the claim record should be excluded from the counts, we are only worried about claim statuses other than "Final" (Open, Reopened).

 

https://www.dropbox.com/s/6urun199qkosbok/AnalyticsReport%20-%20Copy.xlsx?dl=0

 

Thank you kindly!

Hi,

 

Do you only want to see those where the loss date is on or after 1/1/2018 and where the status is other than Final?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

I'd like to consider the entire history of this dataset where status is other than Final.

 

I look forward to learning the technique so I can apply to other projects when needed!

 

I appreciate your continued help!

 

Hi,

 

Is this the result you are expecting?

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

I appreciate your work, this looks promising! 

 

Anyway you can share your techniques and logic please so I can further review?

Hi,

 

You may download my file from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I'm sorry, I can't download the file:

 

Capture.PNG

Hi,

 

Try this link - https://drive.google.com/open?id=1dH-QEuEKH27RxBZlg2SoHfX5JL1zT1Cd

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Very nice work!!

 

I truly appreciate your help with this, and you taught me a thing or two! 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.