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

[Vamshi_R]

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....

[Vamshi_R]

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]

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]

 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... 

[Vamshi_R]

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.

[amitchandak]

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]

Thank you Amit, will check as you suggested.

[amitchandak · Accepted solution]

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.

[PattemManohar]

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