Forum Discussion
How to create a new calculated row from previous row
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?
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
6 Replies
- amitchandak
Super User
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 SalesAdd 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- 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
v-lili6-msftCommunity Support
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
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?
