How to create a new calculated row from previous row

[Anonymous]

Hi,

Can you please help me to achieve the below:

I have a below table:

C1	C2	C3	C4
zzzzz	India	01/01/2019	250
zzzzz	India	01/02/2019	676
zzzzz	India	01/03/2019	33
zzzzz	India	01/04/2019	0
zzzzz	India	01/05/2019	45
zzzzz	India	01/06/2019	232
zzzzz	India	01/07/2019	122
zzzzz	India	01/08/2019	6
zzzzz	India	01/09/2019	45
zzzzz	India	01/10/2019	1334
zzzzz	India	01/11/2019	211
zzzzz	India	01/12/2019	23

 from the above table , i created the matrix table to display the data as:

		January	February	March	April	May	June	July	Augest	Septemper	October	November	December
zzzzz	India	250	676	33	0	45	232	122	6	45	1334	211	23

but I need to display the data as below:

		January	February	March	April	May	June	July	Augest	Septemper	October	November	December
zzzzz	India	250	676	33	0	45	232	122	6	45	1334	211	23
zzzzz	India	250	926	709	33	45	277	354	128	51	1379	1334	211

2nd row is the calculated row. if Jan month I need the same data, if Feb then I need the sum of Jan+Feb data. like for all columns. (Previous month + current month)

can anyone help me to achieve this please?

[v-lili6-msft · Accepted solution]

hi @Anonymous 

You could also try this simple measure

Measure = CALCULATE(SUM('Table'[C4]),DATEADD('Table'[C3].[Date],-1,MONTH))+SUM('Table'[C4])

Then drag it into matrix visual and set "show on rows"

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Hi Lin,

Thank you so much for your solutions. I checked the file you attached and its working as I expected.

I tried to apply the same logic in my original file and not getting the expected output. I am getting the sum of current month data instead of prev month + current month. for example, Jan month data is 200 and  Feb  is 250 then I am getting 500 instead 450.

I couldn't able to attach my file here. I checked all the settings in your file and compared with me but I couldn't get what is the issue. can you help me on this pease?

[Anonymous]

Its working fine now. Thank you.

[amitchandak]

You need to create a measure this month + last month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer])) 
PMTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date Filer],-1,month))) 

New Sales = MTD Sales +PMTD Sales

Add this to your matrix table

In the matrix table. We have a property under values - Show on rows - Switch that on. You will get a similar display with measure name.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

I am new to this, can you please explain what is 

'Date'[Date Filer]

 its any table column  or a pre difined function please? in my table C3 is a date column, how to refer it here ?

[amitchandak]

This my date dimension date and date column is named as date filter.

In case you do not have date dimension, please create one and join with your date in the table

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601