How to calculate number of days between dynamic da...

shelbsassy · ‎02-28-2017

Hi I am working on a calculation to calculate the trended Per Member Per Month (PMPM) which in Excel is (#days in the month/current day in month * calculated PMPM)

This is my setup:

I have a slicer on the month and when you choose either January or February in the slicer, the calculation will change for the trended PMPM.

I have a date table with all the dates in the year, a YEarMonth column, number of days in each month and a calculated column which takes the max date of service for records in the database.

Basically I need to calculate MonthDays/datediff(Maxfor Month,FirstDate)Day (to get the difference between 2/22 being the last service date in the database - and 2/1 the first date of the month, then * PMPM which is a calculation ....all based on which month is being chosen in the filter. I am still playing around with the datefdiff but if someone knows how I can add in the filter to only select the difference in days for that particular month that would be great. I did have datediff working sort of but it was calculating from 1/1 - 2/22 when the feb filter was selected and 1/1-1/31 when the Jan filter was selected.

Here is my date table. Thanks in advance for any help!

CheenuSing · ‎03-01-2017

Hi @shelbsassy

I need few clarifications.

You had indicated "Basically I need to calculate MonthDays/datediff(Maxfor Month,FirstDate)Day".

Just wanted to check with you, is it always difference between 1st of a month and the Lastdate of transaction date in that month.

Or you need to find the difference between the firstDate and LastDate of transaction date in a month. Appreciate if you can post some sample data and the output expected. I have formulated some solution I want to verify before I can post.

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

shelbsassy · ‎03-01-2017

Good morning and thank you for your response.

My dashboard is cumulative monthly and I update the data every Friday so the calculation would be based on the first day of the selected month in the slicer and the max date of the data that is in the database. For example, the first date of January is 1/1 and the max date of data for January is 1/31. So I need to divide 31/31 * the PMPM amount (which is calculated). For February the first date is 2/1 and the latest amount of data is 2/22 and the total number of days in the month is 28 so my calculation would be 28/22*PMPM.

I have a table for dates that I have been trying to figure out but the last few columns arent working as I want them to. I have been trying to calculate the number of days between the first and max date divided by the total number of days in the month but I can't get it to work right.

The other table I have is called Trendcalcs which is calculated from another memebr's post which only has the first date and the max date in it.

Here is a sample of the tables and the slicer that should dynamically change the calculation based on the month. Right now the gauge is the month to date PMPM vs the premium paid but I am tryng to get the trended PMPM based on where we are at in the month to give the Directors a more accurate projected view of the month rather than a cumulative one.

Thanks for any help!

shelbsassy · ‎03-01-2017

So I have made a lot of progress and I almost have it but I am having trouble with the filter.

In my TrendCalcs table I created columns that has Latest day of data and the number of days in the month. I created a measure for MonthPercent = sum(TrendCalcs[DataDay])/sum(TrendCalcs[MonthDays])

I then created a measure to calculate TrendedRaw = TrendCalcs[MonthPercent]*'Members Claims'[PMPM] whch is kind of working but the problem is that the percentage is being summed as an aggregate. So where the percentage for January is 100% or 1 and the percentage for Feb is .79 and it is aggregating the MonthPercent as .90 across the board. How can I filter on the month and not have it aggregated?

Thanks!