Month Total over Life of Plan - Not just created Month.

[xuxxay88]

Hi,

I'm trying to get a value to total across all months that it is live, but can only get it on the month calculated. 

I have a scenario where I have a PlansTable that is connected to my Calendar table via the created date. If I drop just Month Year and Plan Number into a table, it only shows one line for the plan for the month it was sold.

I've created measures for the created date, start date, end date and "removed filters" to get the plans to display across all months.

I'm trying to calculate how much is earned per month over the length of the plan from the plan via Maturity % (Days Ealpsed (start date - Present Date) / Plan Length) based on the amount in the "Fund".

Example, if the plan was 10 days long, the fund is $20 and the plan is 5 days in then the "Earned" amount is $10 as it's at 50% maturity.

I can calculate the Earned Amount for each Month at a plan level OK, but not at the Month Total or overall Total (Not worried about overall total just yet). I understand why the total is wrong - because it is calculating the Maturity % total against the Fund total for the month.

I can get it to total correctly for one month (Earned Month 2) - the month it was created - but not the rest of them? 

[Cover_Number] is "Plan"

Please advise how I can get this calculated correctly for all months that the plan is live, thank you.

[Anonymous]

Hi @xuxxay88 ,

According to your statement, I think you want to calculate the correct result in Subtotal.

Your measure create a virtual table by summarize, it has only two non-repeating values P00300939A and P00300941A. 

Try this measure.

Earned Month 2 =
SUMX ( VALUES ( 'Calendar'[MonthYearName] ), [Earned Month] )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xuxxay88]

Anyone?

[Anonymous]

Hi @xuxxay88 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file without sensitive data.

Best Regards,

Rico Zhou