Calculation Help with growing column

[macemit]

Hi,

Another calculation quandry.  It'll help if you see a sample first:

Release Date:
May-18
	Months Since Release (M)	Fails (F)	M x F
(May)	5	12	60
(June)	4	15	60
(July)	3	22	66
(August)	2	3	6
(September,now)	1	25	25
			217

It is currently Sept, a product was released in May. That is a 5 month span. Each month since release must be multiplied by failures from that month (counted rows from our database). The example above explains it best. Table can grow indefinitely (in Sept 2019, it'd be a 17 month span, etc..)

The '217' is the sum of the right hand column. That is the only number I'm interested in. In fact, the fails (middle column) do not even need to be visible as distinct numbers, they can be summed to give a single number (77 in this case) if that would make calculating easier. I am hoping that this calculation can possibly be acheived through a single 'function', or measure without resorting to a new, growing table. 

I have to do this for multiple products, so obviously the release month will usually vary.

Thanks.

[v-frfei-msft]

Hi @macemit,

I made one sample for your reference. If it doesn't meet your requirement, kindly share your sample data or pbix to me.

1. Enter the sample data and create a calculated column in the table.

Month = FORMAT(RELAEASE[date],"mmmm")

2. Create the measures as below.

Months Since Release = DATEDIFF(MAX(RELAEASE[date]),TODAY(),MONTH)+1

M = [Months Since Release]*SUM(RELAEASE[Fail])

M x F = SUMX(RELAEASE,[M])

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @macemit,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[PattemManohar]

Give a try with this please...

GrandTotal = SUMX(MonthsFails,MonthsFails[Months Since Release (M)]*MonthsFails[Fails (F)])

[macemit]

Thanks. That helps in part. However, is there a method of deriving the months since release without resorting to its own column? Or resorting to columns at all. Ideally, I'd like to have only 2 variables: the release month and total failures. Surely there is a formula that will not require a new table or column.