Copy row values from Matrix and Insert to other row then perform aggregation

[JorgeAbiad]

Hello PBI Experts,

Is it possible to copy row values from matrix and then insert to other row to perform some calculation.

In the pic above, MeasureA will have to be completed.

MeasureA(Apr)=  MeasureA(Mar)+ MeasureC(April)

MeasureA(May)=MeasureA(Apr)+MeasureC(May)

This process is continued until value for MeasureA(Dec) is achieved

Please help

Regards,

JorgeAbiad

[AllisonKennedy]

You could use CALCULATE and DATEADD to get the value of MEASURE A for -1 Month and then add that to the the value of Measure C for the current month. I would recommend creating an additional measure though to make it work so you don't have to deal with circular references and variables. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[JorgeAbiad]

Hello @AllisonKennedy ,

Let me rephrase my scenario, I have a matrix below, the measure is Current(RVO+CDP).

What I want to achieve is to get an accumulated measure from out of this measure. This will then be place in another matrixwith the same columns.

In the new matrix,  Jan=1522,Feb=1522+1363,Mar=Feb(value)+1205,Apr=Mar(value)+556, and so on...

How do I right the correct Dax for this?

Thanks.

Regards,

JorgeAbiad

[amitchandak]

@JorgeAbiad , you have to create a cumulative measure. Prefer a Date calendar

Cumm Sales = CALCULATE([RVO]+[CDP],filter(date,date[date] <=max(Table[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[AllisonKennedy]

Can you use the DATESYTD()  DAX function? Are your months coming from a DimDate or Calendar table? If so I would recommend using time intelligence dax here to get cumulative results. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv