Substract first month value from rest all months

[Anonymous]

Hello Friends,

I am struggle to get logic. Month & Sales

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Dec
21	25	35	45	65	69	70	80	87	100

My requirement is

A= 21(Jan)

B=100(Dec)

([Sales]-A)/(B-A)

The result should be

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Dec
0	(25-21)/(100-21)=0.****	(35-21)/(100-21)=0.****	45	65	69	70	80	87	100

Kindly help me on solution

[smpa01 · Accepted solution]

@Anonymous  if you have a table like this

month	value
jan	21
feb	25
march	35
apr	45
may	65
june	69
july	70
aug	80
sep	87
oct	90
nov	92
dec	100

and the final fraction to follow the following logic

A= 21(Jan)

B=100(Dec)

([Sales]-A)/(B-A)

You can reach what you need by using following measures

Sales:= SUMX(X,X[value])

only jan sales (A) := CALCULATE([Sales],ALL(X),X[month]="jan")

only dec sales (B):= CALCULATE([Sales],ALL(X),X[month]="dec")

Numerator (Sales-A):= [Sales]-[only jan sales (A)]

Denominator (B-A):= [only dec sales (B)]-[only jan sales (A)]

Fraction:= [Numerator (Sales-A)]/[Denominator (B-A)]

[Anonymous]

Super,

Works for me.

Thank you for your quick help and support.

Regards,

Babu

[Anonymous]

Hi @Anonymous ,

IF your data is really organized in the way it is shown (it seems unlikely), so a column for Jan, another column for Feb and so on, 

Then you can just add columns to your table with the calculation as you described.

For instance for FEB : Calc Feb = Divide((sum(Data[Feb]) - Sum(Data[Jan])),sum(data[dec])-sum(Data[Jan]))

Otherwise it is better to show how your data is organized, and also why Sep and Oct are missing.

Jan 

[Anonymous]

Dear JustJan,

Sorry for not elobarate requirement to you, but smpa01 solution working for me.

thank you for your quick response.

Regards,

Babu