Calculate a rebased series

1
votes

In Power BI, I am trying to create a box that shows a line graph of a number of equity indeces over time, rebased so that the original value is 1. I am using a slicer.

The idea is that the user will select a date range and see the performance of the various markets, all starting at unity.

I am very much a novice DAX user.

The below returns garbage values on the order of 500K

Rebased = 

VAR
__FirstValue = CALCULATE(SUM('Equity Markets (2)'[Value]),FIRSTDATE('Equity Markets (2)'[Date]))

VAR
__CurrentValue = SUM('Equity Markets (2)'[Value])

RETURN
CALCULATE(DIVIDE(__CurrentValue,__FirstValue))

REQUESTED DATA SAMPLE:

Date    S&P/ASX 300 - PRICE INDEX   S&P 500 COMPOSITE - PRICE INDEX DAX 30 PERFORMANCE - PRICE INDEX
15/05/2009  3768.03 882.88  4737.5
18/05/2009  3729.82 909.71  4851.96
19/05/2009  3810.01 908.13  4959.62
20/05/2009  3817.48 903.47  5038.94
21/05/2009  3807.67 888.33  4900.67
22/05/2009  3756.13 887 4918.75
25/05/2009  3732.7  887 4918.45
26/05/2009  3782.56 910.33  4985.6
27/05/2009  3796.1  893.06  5000.77
28/05/2009  3751.3  906.83  4932.88
29/05/2009  3813.64 919.14  4940.82
1/06/2009   3890.79 942.87  5142.56
2/06/2009   3950.74 944.74  5144.06
3/06/2009   4012.13 931.76  5054.53
4/06/2009   3930.65 942.46  5064.8
5/06/2009   3967.33 940.09  5077.03
8/06/2009   3967.33 939.14  5004.72
9/06/2009   3931.38 942.43  4997.86
10/06/2009  4019.95 939.15  5051.18
11/06/2009  4042.7  944.89  5107.26
12/06/2009  4057.8  946.21  5069.24
15/06/2009  4027.6  923.72  4889.94
16/06/2009  3957.63 911.97  4890.72
17/06/2009  3899.72 910.71  4799.98
18/06/2009  3887.58 918.37  4837.48
19/06/2009  3895    921.23  4839.46
22/06/2009  3913.1  893.04  4693.4
23/06/2009  3791.74 895.1   4707.15
24/06/2009  3801.79 900.94  4836.01
25/06/2009  3850.9  920.26  4800.56
26/06/2009  3899.44 918.9   4776.47
29/06/2009  3882.2  927.23  4885.09
30/06/2009  3948.68 919.32  4808.64
1/07/2009   3869.44 923.33  4905.44
2/07/2009   3873.62 896.42  4718.49
3/07/2009   3824.65 896.42  4708.21
6/07/2009   3780.2  898.72  4651.82
7/07/2009   3763    881.03  4598.19
8/07/2009   3762.98 879.56  4572.65
9/07/2009   3758.1  882.68  4630.07
10/07/2009  3788.6  879.13  4576.31
13/07/2009  3732.7  901.05  4722.34
14/07/2009  3861.41 905.84  4781.69
15/07/2009  3918.72 932.68  4928.44
16/07/2009  3989.7  940.74  4957.19
17/07/2009  3994.3  940.38  4978.4
20/07/2009  4043.6  951.13  5030.15
21/07/2009  4044.6  954.58  5093.97
22/07/2009  4062.5  954.07  5121.56
23/07/2009  4059.6  976.29  5247.28
24/07/2009  4084.79 979.26  5229.36
27/07/2009  4133.98 982.18  5251.55
28/07/2009  4162.7  979.62  5174.74
29/07/2009  4136.3  975.15  5270.32
30/07/2009  4183.6  986.75  5360.66
31/07/2009  4238.24 987.48  5332.14
3/08/2009   4259.2  1002.63 5426.85
4/08/2009   4304.21 1005.65 5417.02
5/08/2009   4259.54 1002.72 5353.01
6/08/2009   4320.41 997.08  5369.98
7/08/2009   4294.21 1010.48 5458.96
10/08/2009  4300.06 1007.1  5418.12
11/08/2009  4327.46 994.35  5285.81
12/08/2009  4338.93 1005.81 5350.09
13/08/2009  4432.42 1012.73 5401.11
14/08/2009  4458.59 1004.09 5309.11
17/08/2009  4385.47 979.73  5201.61
18/08/2009  4377.34 989.67  5250.74
19/08/2009  4371.38 996.46  5231.98
20/08/2009  4374.91 1007.37 5311.06
21/08/2009  4288.36 1026.13 5462.74
24/08/2009  4422.93 1025.57 5519.75
25/08/2009  4403.84 1028    5557.09
26/08/2009  4452.72 1028.12 5521.97
27/08/2009  4448.66 1030.98 5470.33
28/08/2009  4487.13 1028.93 5517.35
31/08/2009  4476.71 1020.62 5464.61
1/09/2009   4511.22 998.04  5327.29
2/09/2009   4435.57 994.75  5319.84
3/09/2009   4429.02 1003.24 5301.42
4/09/2009   4435.48 1016.4  5384.43
7/09/2009   4454.61 1016.4  5463.51
8/09/2009   4524.33 1025.39 5481.73
9/09/2009   4523.49 1033.37 5574.26
10/09/2009  4571.37 1044.14 5594.77
11/09/2009  4596.2  1042.73 5624.02
14/09/2009  4532.22 1049.34 5620.24
15/09/2009  4541.84 1052.63 5628.98
16/09/2009  4650.89 1068.76 5700.26
17/09/2009  4715.33 1065.49 5731.14
18/09/2009  4694.99 1068.3  5703.83
21/09/2009  4678.74 1064.66 5668.65
22/09/2009  4665.72 1071.66 5709.38
23/09/2009  4736.2  1060.87 5702.05
24/09/2009  4703.11 1050.78 5605.21
25/09/2009  4714.38 1044.38 5581.41
28/09/2009  4677.71 1062.98 5736.31
29/09/2009  4752.17 1060.61 5713.52
30/09/2009  4742.05 1057.08 5675.16
1/10/2009   4700.57 1029.85 5554.55
2/10/2009   4600.96 1025.21 5467.9
5/10/2009   4572.45 1040.46 5508.85
6/10/2009   4591.55 1054.72 5657.64
7/10/2009   4695.04 1057.58 5640.75
8/10/2009   4767.6  1065.48 5716.54
9/10/2009   4753.45 1071.49 5711.88
12/10/2009  4740.73 1076.19 5783.23
13/10/2009  4786.47 1073.19 5714.31
14/10/2009  4832.16 1092.02 5854.14
15/10/2009  4860.43 1096.56 5830.77
16/10/2009  4837.58 1087.68 5743.39
19/10/2009  4794.56 1097.91 5852.56
20/10/2009  4847.45 1091.06 5811.77
21/10/2009  4839.81 1081.4  5833.49
22/10/2009  4813.5  1092.91 5762.93
23/10/2009  4859.37 1079.6  5740.25
26/10/2009  4830.48 1066.95 5642.16
27/10/2009  4753.23 1063.41 5635.02
28/10/2009  4683.99 1042.63 5496.27
29/10/2009  4572.36 1066.11 5587.45
30/10/2009  4642.06 1036.19 5414.96
2/11/2009   4539.4  1042.88 5430.82
3/11/2009   4531.22 1045.41 5353.35
4/11/2009   4539.91 1046.5  5444.23
5/11/2009   4508.57 1066.63 5480.92
6/11/2009   4594.77 1069.3  5488.25
9/11/2009   4675.28 1093.08 5619.72
10/11/2009  4733.95 1093.01 5613.2
11/11/2009  4757.52 1098.51 5668.35
12/11/2009  4748.47 1087.24 5663.96
13/11/2009  4707.82 1093.48 5686.83
16/11/2009  4756.15 1109.3  5804.82
17/11/2009  4730.34 1110.32 5778.43
18/11/2009  4739.75 1109.8  5787.61
19/11/2009  4749.77 1094.9  5702.18
20/11/2009  4686.86 1091.38 5663.15
23/11/2009  4717.98 1106.24 5801.48
24/11/2009  4686.43 1105.65 5769.31
25/11/2009  4722.86 1110.63 5803.02
26/11/2009  4709.95 1110.63 5614.17
27/11/2009  4574.22 1091.49 5685.61
30/11/2009  4701.52 1095.63 5625.95
1/12/2009   4719.43 1108.86 5776.61
2/12/2009   4763.35 1109.24 5781.68
3/12/2009   4775.77 1099.92 5770.35
4/12/2009   4704.64 1105.98 5817.65
7/12/2009   4678.3  1103.25 5784.75
8/12/2009   4672.33 1091.94 5688.58
9/12/2009   4638.93 1095.95 5647.84
10/12/2009  4607.37 1102.35 5709.02
11/12/2009  4635.65 1106.41 5756.29
14/12/2009  4654    1114.11 5802.26
15/12/2009  4673.67 1107.93 5811.34
16/12/2009  4661.6  1109.18 5903.43
17/12/2009  4670.1  1096.08 5844.44
18/12/2009  4649.39 1102.47 5831.21
21/12/2009  4634.18 1114.05 5930.53
22/12/2009  4702.52 1118.02 5945.69
23/12/2009  4736.92 1120.59 5957.44
24/12/2009  4788.06 1126.48 5957.44
25/12/2009  4788.06 1126.48 5957.44
28/12/2009  4788.06 1127.78 6002.92
29/12/2009  4842.85 1126.2  6011.55
30/12/2009  4831.36 1126.42 5957.43
31/12/2009  4868.2  1115.1  5957.43
1/01/2010   4868.2  1115.1  5957.43
4/01/2010   4874.29 1132.99 6048.3
5/01/2010   4922.6  1136.52 6031.86
6/01/2010   4920.72 1137.14 6034.33
7/01/2010   4899.66 1141.69 6019.36
8/01/2010   4912.02 1144.98 6037.61
11/01/2010  4950.25 1146.98 6040.5
12/01/2010  4899.7  1136.22 5943
13/01/2010  4868.47 1145.68 5963.14
14/01/2010  4897.71 1148.46 5988.88
15/01/2010  4899.16 1136.03 5875.97
18/01/2010  4910.02 1136.03 5918.55
19/01/2010  4860.68 1150.23 5976.48
20/01/2010  4866.92 1138.04 5851.53
21/01/2010  4825.16 1116.48 5746.97
22/01/2010  4748.51 1091.76 5695.32
25/01/2010  4716.07 1096.78 5631.37
26/01/2010  4716.07 1092.17 5668.93
27/01/2010  4643.07 1097.5  5643.2
28/01/2010  4671.51 1084.53 5540.33
29/01/2010  4567.85 1073.87 5608.79
1/02/2010   4519.78 1089.19 5654.48
2/02/2010   4601.64 1103.32 5709.66
3/02/2010   4643.84 1097.28 5672.09
4/02/2010   4616.86 1063.11 5533.24
5/02/2010   4508.74 1066.19 5434.34
8/02/2010   4516.03 1056.74 5484.85
9/02/2010   4498.78 1070.52 5498.26
10/02/2010  4507.42 1068.13 5536.37
11/02/2010  4549.47 1078.47 5503.93
12/02/2010  4558.41 1075.51 5500.39
15/02/2010  4540.85 1075.51 5511.1
16/02/2010  4562.72 1094.87 5592.12
17/02/2010  4662.13 1099.51 5648.34
18/02/2010  4649.19 1106.75 5680.41
19/02/2010  4629.54 1109.17 5722.05
22/02/2010  4711.28 1108.01 5688.44
23/02/2010  4711.93 1094.6  5604.07
24/02/2010  4642.88 1105.24 5615.51
25/02/2010  4588.5  1102.94 5532.33
26/02/2010  4631.09 1104.49 5598.46
1/03/2010   4679.14 1115.71 5713.51
2/03/2010   4694.54 1118.31 5776.56
3/03/2010   4727.61 1118.79 5817.88
4/03/2010   4742.7  1122.97 5795.32
5/03/2010   4759.05 1138.7  5877.36
8/03/2010   4800.57 1138.5  5875.91
9/03/2010   4812.82 1140.45 5885.89
10/03/2010  4812.98 1145.61 5936.72
11/03/2010  4807.29 1150.24 5928.63
12/03/2010  4811.54 1149.99 5945.11
15/03/2010  4777.87 1150.51 5903.56
16/03/2010  4790.56 1159.46 5970.99
17/03/2010  4846.04 1166.21 6024.28
18/03/2010  4856.06 1165.83 6012.31
19/03/2010  4865.63 1159.9  5982.43
22/03/2010  4823.58 1165.81 5987.5
23/03/2010  4867.47 1174.17 6017.27
24/03/2010  4883.89 1167.72 6039
25/03/2010  4877.32 1165.73 6132.95
26/03/2010  4888.05 1166.59 6120.05
29/03/2010  4888.62 1173.22 6156.85
30/03/2010  4907.95 1173.27 6142.45
31/03/2010  4868.4  1169.43 6153.55
1/04/2010   4900.4  1178.1  6235.56
2/04/2010   4900.4  1178.1  6235.56
5/04/2010   4900.4  1187.44 6235.56
6/04/2010   4946.4  1189.44 6252.21
7/04/2010   4953.66 1182.45 6222.41
8/04/2010   4930.79 1186.44 6171.83
9/04/2010   4941.06 1194.37 6249.7
12/04/2010  4977.79 1196.48 6250.69
13/04/2010  4945.51 1197.3  6230.83
14/04/2010  4987.79 1210.65 6278.4
15/04/2010  4995.04 1211.67 6291.45
16/04/2010  4978.26 1192.13 6180.9
19/04/2010  4909.63 1197.52 6162.44
20/04/2010  4919.88 1207.17 6264.23
21/04/2010  4948.84 1205.94 6230.38
22/04/2010  4902.88 1208.67 6168.72
23/04/2010  4877.21 1217.28 6259.53
26/04/2010  4877.21 1212.05 6332.1
27/04/2010  4875.89 1183.71 6159.51
28/04/2010  4818.41 1191.36 6084.34
29/04/2010  4781.06 1206.78 6144.91
30/04/2010  4801.96 1186.69 6135.7
3/05/2010   4779.39 1202.26 6166.92
4/05/2010   4730    1173.6  6006.86
5/05/2010   4666.66 1165.9  5958.45
6/05/2010   4567.05 1128.15 5908.26
7/05/2010   4474.56 1110.88 5715.09
10/05/2010  4592.91 1159.73 6017.91
11/05/2010  4542.12 1155.79 6037.71
12/05/2010  4567.28 1171.67 6183.49
13/05/2010  4646.71 1157.44 6251.97
14/05/2010  4605.86 1135.68 6056.71

Current code:

Rebase3 =
VAR BASELINE_VALUE = CALCULATE ( SUM ('Equity Markets (2)'[Value]), FIRSTDATE ( ALLSELECTED ('Equity Markets (2)'[Date]) ) )

VAR VALUE_TO_COMPARE = SUM ('Equity Markets (2)'[Value])

RETURN
DIVIDE(VALUE_TO_COMPARE, BASELINE_VALUE)

screenshot

2
powerbidaxrebase
Is "Rebased" a measure or a calculated column? – RADO
A created column... should it have been a measure? – Tikhon
Yes - columns can't respond to slicers. If you want to get good advice, please post a data sample or share your pbix book. – RADO
I didn't know I could upload files to stack overflow... I still have the same code but now in a measure - still no joy, the thing evaluates to 1.00 at every date, it seems – Tikhon
I know the answer, but it'll be better if you can give me data to work with. Either just add data as text to your question, or upload pbix to an online folder like google drive, and paste a shared link. – RADO

2 Answers

4
votes

I simplified your names, and used only S&P500 (second index) column as an example - others will work the same way.

Create a measure:

Rebased S&P500 =
    VAR 
        Original_Value =
        CALCULATE ( SUM ( Data[SP500] ), FIRSTDATE ( ALLSELECTED ( Data[Date] ) ) )
    VAR 
        Current_Value = SUM ( Data[SP500] )
    RETURN
        DIVIDE ( Current_Value, Original_Value )

Result (I used Power Pivot table, but charts in Power BI will work the same way):

enter image description here

How it works:

  • First, it must be a measure. As mentioned in the comments, calculated columns are essentially static data, they can't respond to user actions.
  • Second, to calculate original value you must know the first date in the selected range of dates. This is accomplished by using ALLSELECTED, which provides a list of all selected dates to function FIRSTDATE.
  • Finally, we just divide current value by original value. No need to use CALCULATE here.
-1
votes

Thanks for mentioning you're a novice, you've created some well known novice mistakes in here.

The first one is the use of a VAR. This is very important, and it's also counter-intuitive. Once you define a VAR it is actually a constant and it's not going to change by the user's selection!. The reason for that is that you lose the context (all the queries in DAX exists in a context). The solution is to define a MEASURE. Once you do that you can work out the issues. That's why you get garbage results.

https://dax.guide/st/var/

and

https://www.sqlbi.com/articles/variables-in-dax/

The good news is once you've managed to understand this you pretty much work out the hardest hurdle in DAX.