Help with a calculation from Excel to calculate trended PMPM

[shelbsassy]

I have a formula in an excel workbook that I update every month to try and get the trended PMPM (Per Member Per Month) based the total number of days in the month and it varies based on the day of the month.  For example:

The formula for February is =(28/COUNTA(A3:A25))*C28 where A3-A25 are the days of the month (each day I insert a row for the day before's claims) so 2/1 2/2 2/3 etc.  The C28 is the month to date PMPM.   Does anyone know how I can make this a dynamic calculation in power bi?  THe part I don't understand is how to automatically calculate the number of days in the month and then how to perform the calculation based on where we are at in days in the month.

Thanks for any help.

[Sandra234]

The best location develops in lots of scenarios, Zoran Dieann There many reasons felines might stand up to entering the litterbox.

[Phil_Seamark]

This sounds achieveable.  Any chance you can show an example of your data, including an example of what the values should look like once you have this calcuation in DAX.

[shelbsassy]

Hi there, thanks for your response~!  Here is a screenshot of the Excel data I am using.  

I am trying to get the 28-Day Trended Part D PMPM which is based on the number of days in the month and is calculated by what day of the month it is.  The formula behind it is =(28/COUNTA(A3:A28))*C31 and C31 is the Month to date PMPM.  I built a date table with 365 rows, 2 columns that has date and then daysinmonth |  1/1/2017   |    31|   , etc.  Let me know if you need more clarification.  Thanks for looking into this!  I really appreciate it!

[shelbsassy]

So I found this awesome post by https://community.powerbi.com/t5/Desktop/Measure-to-show-days-in-the-month-automatically/td-p/53790 ...v-haibl-msft and I have created a column in my dates table that shows the number of days in each month.  Now I just need help with calculating it based on what day of the month I am refreshing the data (every Friday.)

Thanks!