cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Vamshi_R Frequent Visitor
Frequent Visitor

Need Help!! Calculating Current open claims for every month.

I can calculate new cliams and closed claims for every month current and past, however need to calculate below calculation to show current open cliams for any month in report...

 

Current Open cliams = Current Opencliams previousmonth+ new claims - closed cliams 

 

Any help will be great....

1 ACCEPTED SOLUTION

Accepted Solutions
amitchandak Super Contributor
Super Contributor

Re: Need Help!! Calculating Current open claims for every month.

Try like

calculate(count(claims[id]),filter(claims,claims[Open Date]<ENDOFMONTH('Date'[Date Filer]) && claims[Close Date]>ENDOFMONTH('Date'[Date Filer]) )

Where date is date table in power bi

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Need Help!! Calculating Current open claims for every month.

@Vamshi_R Try creating the measure for "Current Open Claims" and use this measure along with Time-Intelligence DAX functions



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





amitchandak Super Contributor
Super Contributor

Re: Need Help!! Calculating Current open claims for every month.

Try like

calculate(count(claims[id]),filter(claims,claims[Open Date]<ENDOFMONTH('Date'[Date Filer]) && claims[Close Date]>ENDOFMONTH('Date'[Date Filer]) )

Where date is date table in power bi

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

View solution in original post

Vamshi_R Frequent Visitor
Frequent Visitor

Re: Need Help!! Calculating Current open claims for every month.

Situation as an example below...

Open claims EOM December = 573,  New cliams created and also Claims Reopened in January = 175, Claims Closed = 150. Now, I need to write a DAX to get Current Open claims = 573+175- 150 = 598 for the month of January.

amitchandak Super Contributor
Super Contributor

Re: Need Help!! Calculating Current open claims for every month.

Any thing which opens before this month-end and not closed before this month-end is open. That is what I was trying to find. 

 

It is the same as what you are saying, just a changed way to implement. 

Vamshi_R Frequent Visitor
Frequent Visitor

Re: Need Help!! Calculating Current open claims for every month.

 Hi Amit, you are correct I tried and trying to put the number to report, however team want to replicate similar to the screenshot shown in attached... Capture1.PNG

amitchandak Super Contributor
Super Contributor

Re: Need Help!! Calculating Current open claims for every month.

If open claim no is correct. This how you have to deal with open and close.

Make sure you have a date dimensions table. Marked as Date Table in Power BI Model.

Join it both open and close date. One or both can be inactive joins. As per your current join.

Refer to the article below on how to use userelation to shift date.

 

https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p...

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

Vamshi_R Frequent Visitor
Frequent Visitor

Re: Need Help!! Calculating Current open claims for every month.

Thank you Amit, will check as you suggested.

Vamshi_R Frequent Visitor
Frequent Visitor

Re: Need Help!! Calculating Current open claims for every month.

Using Amit's solution I was able to find current month open claims, I found data all these days they were counting which is manual was wrong, now they dont want to change previous data numbers, however calculate current open claims using dax keeping previous monthly number fixed (till June 2019).

I need to write a dax to calculate Previous month current open claims which should pickup from a table which I will create with previous historic data with End of the month date and number of claims as columns, and from July onwards need to write Amith's code.

 

Any ideas will help me great ..... thank you all.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 148 members 1,588 guests
Please welcome our newest community members: