Measure to form the sum with variables

[Anonymous]

Dear Power BI community,

I have the following data set. To make my problem easier to understand, I have chosen this representation.

	12019	22019	32019	42019	52019	62019	72019	82019	92019	102019	112019	122019
VJ	1	1	1	2	3	1	(1)	1	1	0	0	0
R01	300	12	2	1	0	0	0	0	0	0	0	0
R02	0	500	13	2	1	(4)	0	0	0	0	0	0
R03	0	0	800	20	(6)	(10)	0	1	0	0	0	0
R04	0	0	0	700	30	(3)	2	1	1	0	0	0
R05	0	0	0	0	800	15	4	2	1	1	1	0
R06	0	0	0	0	0	1.000	25	2	0	0	1	0
R07	0	0	0	0	0	0	700	20	0	(3)	0	0
R08	0	0	0	0	0	0	0	600	45	(1)	3	0
R09	0	0	0	0	0	0	0	0	900	30	1	0
R10	0	0	0	0	0	0	0	0	0	900	50	12
R11	0	0	0	0	0	0	0	0	0	0	1.000	40
R12	0	0	0	0	0	0	0	0	0	0	0	1.100
Uother	0	0	0	0	0	0	0	0	0	0	0	0

I am looking for a measure with which I can add up the lines above, up to the line before the current month.

E.g.:

If I filter "42019" I want to get "25" (2 + 1 + 2 + 20 = the values from 42019 VJ+R01+R02+R03).

If I filter "52019" then "28" (3 + 0 + 1-6 + 30 = the values of 52019 VJ+R01+R02+R03+R05) and so on.

This should also work if I choose several months at once.

E.g. "12019 to 32019" and then get the value "30" (1 + 1 + 1 + 12 + 2 + 13)

Do you have an idea how I can set up such a measure?

Thanks in advance for your help

Kind regards,

Jinu

[v-janeyg-msft · Accepted solution]

Hi,  @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can create two column and a measure to calculate the desired result.

Like this:

index2 =
RANKX ( ALL ( 'Table' ), 'Table'[R],, ASC, DENSE )

month = LEFT('Table'[Attribute],SEARCH("2019",[Attribute])-1)
//Need to change the format to number

total = SUMX(FILTER('Table',[index2]<[month]||[index2]=14),[Value])

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[amitchandak]

@Anonymous , is this your source data format?

if so, please unpivot https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous]

Hi,

no that is not the source data format.

The shown one is to explain the problem.

I already unpivot it, but that doesn't help me too