DAX to create cumulative price table

[manojsv16]

Hello,

I have been working on to create a cumulative price table shown below. 

I wanted to calculate cumulative price based on the below calculated columns ( for ex Cumulative Price 461.8 = SUM(Price 2018Q1) Calculated column). I am struck with how to pivot the calculate price to the corresponding year and quarter based on the calculated column name. 

 I would appreciate any suggestions/help in getting achieve the below output table. I have also provided the input table and DAX formula for calculated column

Please let me know if you need any further information.

Year	Quarter	Cumulative Price
2018	Q1	461.18
2018	Q2	386
2018	Q3	317.3
2018	Q4	292.6
2019	Q1	261.775
2019	Q2	195.25
2019	Q3	176.75
2019	Q4	158.05
2020	Q1	150.25
2020	Q2	131.75
2020	Q3	97.55
2020	Q4	28.375

Following fields are the acutal input data

ID, Name, Year, Quarter Price

Remaining fields are calculated columns

Target  = SWITCH( CONCATENATE(Price_data[Year],Price_data[Quarter]), "2018Q1",1, "2018Q2",2, "2018Q3",3, "2018Q4",4, "2019Q1",5, "2019Q2",6, "2019Q3",7, "2019Q4",8, "2020Q1",9, "2020Q2",10, "2020Q3",11, "2020Q4",12 )

Price 2018 Q1 = IF(Price_data[Target] >= 1,         IF(Price_data[Price] > 0,            DIVIDE(Price_data[Price],4),0),         0)

Price 2018 Q2 = IF(Price_data[Target] >= 2,         IF(Price_data[Price] > 0,            DIVIDE(Price_data[Price],4),0),         0)

Input Data

ID            Name      Year       Quarter Price      Target Price2018Q1 Price 2018Q2 Price 2018 Q3 Price 2018 Q4 Price 2019 Q1 

1	Asset 1	2018	Q1	50.4	1	12.6	0	0	0	0	0	0	0	0	0	0	0
2	Asset 2	2018	Q1	250	1	62.5	0	0	0	0	0	0	0	0	0	0	0
3	Asset 3	2018	Q2	250	2	62.5	62.5	0	0	0	0	0	0	0	0	0	0
4	Asset 4	2018	Q2	25	2	6.25	6.25	0	0	0	0	0	0	0	0	0	0
5	Asset 5	2018	Q3	89	3	22.25	22.25	22.25	0	0	0	0	0	0	0	0	0
6	Asset 6	2018	Q3	10	3	2.5	2.5	2.5	0	0	0	0	0	0	0	0	0
7	Asset 7	2018	Q4	78.23	4	19.5575	19.5575	19.5575	19.5575	0	0	0	0	0	0	0	0
8	Asset 8	2018	Q4	45	4	11.25	11.25	11.25	11.25	0	0	0	0	0	0	0	0
9	Asset 9	2019	Q1	250	5	62.5	62.5	62.5	62.5	62.5	0	0	0	0	0	0	0
10	Asset 10	2019	Q1	15	5	3.75	3.75	3.75	3.75	3.75	0	0	0	0	0	0	0
11	Asset 11	2019	Q2	15	6	3.75	3.75	3.75	3.75	3.75	3.75	0	0	0	0	0	0
12	Asset 12	2019	Q2	60	6	15	15	15	15	15	15	0	0	0	0	0	0
13	Asset 13	2019	Q3	15	7	3.75	3.75	3.75	3.75	3.75	3.75	3.75	0	0	0	0	0
14	Asset 14	2019	Q3	60	7	15	15	15	15	15	15	15	0	0	0	0	0
15	Asset 15	2019	Q4	15	8	3.75	3.75	3.75	3.75	3.75	3.75	3.75	3.75	0	0	0	0
16	Asset 16	2019	Q4	15	8	3.75	3.75	3.75	3.75	3.75	3.75	3.75	3.75	0	0	0	0
17	Asset 17	2020	Q1	15	9	3.75	3.75	3.75	3.75	3.75	3.75	3.75	3.75	3.75	0	0	0
18	Asset 18	2020	Q1	60	9	15	15	15	15	15	15	15	15	15	0	0	0
19	Asset 19	2020	Q2	34.54	10	8.635	8.635	8.635	8.635	8.635	8.635	8.635	8.635	8.635	8.635	0	0
20	Asset 20	2020	Q2	102.34	10	25.585	25.585	25.585	25.585	25.585	25.585	25.585	25.585	25.585	25.585	0	0
21	Asset 21	2020	Q3	189.56	11	47.39	47.39	47.39	47.39	47.39	47.39	47.39	47.39	47.39	47.39	47.39	0
22	Asset 22	2020	Q3	87.23	11	21.8075	21.8075	21.8075	21.8075	21.8075	21.8075	21.8075	21.8075	21.8075	21.8075	21.8075	0
23	Asset 23	2020	Q4	94.23	12	23.5575	23.5575	23.5575	23.5575	23.5575	23.5575	23.5575	23.5575	23.5575	23.5575	23.5575	23.5575
24	Asset 24	2020	Q4	19.2	12	4.8	4.8	4.8	4.8	4.8	4.8	4.8	4.8	4.8	4.8	4.8	4.8

[v-yuezhe-msft · Accepted solution]

@manojsv16,

Create all the calculated columns in Power BI Desktop query editor.

For target:

=if Number.ToText([Year])&[Quarter]="2018Q1" then 1 else
if Number.ToText([Year])&[Quarter]="2018Q2" then 2 else
if Number.ToText([Year])&[Quarter]="2018Q3" then 3 else
if Number.ToText([Year])&[Quarter]="2018Q4" then 4 else
if Number.ToText([Year])&[Quarter]="2019Q1" then 5 else
if Number.ToText([Year])&[Quarter]="2019Q2" then 6 else
if Number.ToText([Year])&[Quarter]="2019Q3" then 7 else
if Number.ToText([Year])&[Quarter]="2019Q4" then 8 else
if Number.ToText([Year])&[Quarter]="2020Q1" then 9 else
if Number.ToText([Year])&[Quarter]="2020Q2" then 10 else
if Number.ToText([Year])&[Quarter]="2020Q3" then 11 else
12

For Price 2018 Q1:

=if [Target] >= 1 
then if [Price] > 0 then [Price]/4 else 0
else 0

For Price 2018 Q2:

=if [Target] >= 2 
then if [Price] > 0 then [Price]/4 else 0
else 0

In Query Editor, select all Price 201X QX columns, then unpivot these columns.


After you apply changes to Power BI report, create the following calculated columns in your table.

NewYear = MID('Price_data'[Attribute],7,4)

NewQr = RIGHT('Price_data'[Attribute],2)

Create table visual as below.


Regards,
Lydia