Replace Value of Specific Row, if Previous Month Row Value is not 0

[Basic0701]

Hi, 

as the title says, I'm trying to replace the value based on a value of the account on the previous month, 

it would look like something like this on the output

I'm using a single table for the total per month, however the requirements is to carry over "Account 4"'s value of current month, and replace "Account 0" with the value of "Account 4" for the Next Month. 

is there a way to do this?  

Thanks in Advance. 

[Anonymous · Accepted solution]

Hi,@Basic0701 

You can use the following method to meet your needs:

 1.Create a date table like : 

Date = CALENDARAUTO()

 and make relationships with your existed table.

2.Create the first measure like this :

Measure = IF(CALCULATE(SUM('Table'[Values]),DATEADD('Date'[Date],-1,MONTH))<>0 &&MAX('Table'[Accounts])="Account 0",CALCULATE(SUM('Table'[Values]),DATEADD('Date'[Date],-1,MONTH),FILTER(ALL('Table'[Accounts]),'Table'[Accounts]="Account 4")),SUM('Table'[Values]))

3.Create the second measure to deal with measure total:

Measure 2 = SUMX(VALUES('Table'[Accounts]),[Measure])

Then you can get a result like this :

Here is the demo , please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EUCwgugyX2tLoW4hqw8...

Hope it helps.

Best Regards,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Basic0701]

Hi Caitlyn, 
I tried you sugggested measure, and it worked, however there some details I forgot to include, 

I also attached some explanation to what I did for that detail I forgot to type in this topic. 

hope you can reply on this one too

thank you very much 

[amitchandak]

@Basic0701 , is this your raw data format. We can use this month last month measures using time intelligence if you have date; with help from date table

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

and you can use if like this or change this as per need

if(isblank([last month Sales ]),[MTD Sales ],[last month Sales ])

[Basic0701]

Hi, I tried your sample, and I couldn't get the modification from there yet. 
it does carry over however I need only from one row to replace in another row of next month..

btw thanks for your suggestion I'll keep testing based on it. or maybe I would find another solution for this throughout my testing. 

[Basic0701]

 "is this your raw data format." 

 and also, Each Account presented in the example has unique  Account Id's, I'm using Matrix Visual based on  this example. *I forgot to include in the context.  

I will try this and get back to you if I got it. 

thanks!