Replace Null with the value of the previous month

[Anonymous]

Hello Community and Thank you for your guidance ,

           I have sales of customer by Month He could have 0 sales for a month  I want to replace it with the sales of the previous Month in Power BI is it possible ?

Cust	Sales	Month
A	0	5-2021
A	1000	4-2021
A	0	3-2021
A	1200	2-2021

the result 

Cust	Sales	Month
A	1000	5-2021
A	1000	4-2021
A	1200	3-2021
A	1200	2-2021

Best Regards 

[amitchandak · Accepted solution]

@Anonymous , Create a date like

Date = "01-" &[Month]

then try a new column

new column =
if([Sales] =0 ,sumx(filter(Table, eomonth([Date],0) = eomonth(earlier([Date]),-1)),[Sales]),[sales])

or measure using time intelligence

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

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

[Anonymous · Accepted solution]

Hi all thank you for your suggestion . I tried your formula but i get this result : 

the amount of sales in june shoud be equal to May and for each Client it should be different I Get the same amount for different Client if the sales = 0 

Do you have any idea 

and thank for this community 

[Anonymous · Accepted solution]

Hi @amitchandak , thank your for youe suggestion , I tried the formula i get the same result Sum all the sales of previous month for all client . 

Do you any idea How to correct it 

best Regards 

[Anonymous]

Thank You all for your help . Great Community 

[ERD]

Hi @Anonymous ,

There might be many options depending on your model, prerequisites and requirements.

Do you need a measure or a calculated column?

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

[v-jayw-msft]

Hi @Anonymous ,

Check the formula.

Column = IF('Table'[Sales ]=0,CALCULATE(SUM('Table'[Sales ]),FILTER('Table','Table'[Month ]=EDATE(EARLIER('Table'[Month ]),-1))),'Table'[Sales ])

 

Best Regards,

Jay

[Anonymous · Accepted solution]

Hi all thank you for your suggestion . I tried your formula but i get this result : 

the amount of sales in june shoud be equal to May and for each Client it should be different I Get the same amount for different Client if the sales = 0 

Do you have any idea 

and thank for this community 

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Modify the measure as below, add client condition.

Column = IF('Table'[Sales]=0,CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]=EDATE(EARLIER('Table'[Date]),-1)&&'Table'[Client]=EARLIER('Table'[Client]))),'Table'[Sales])

Best Regards,

Jay

[amitchandak · Accepted solution]

@Anonymous , Create a date like

Date = "01-" &[Month]

then try a new column

new column =
if([Sales] =0 ,sumx(filter(Table, eomonth([Date],0) = eomonth(earlier([Date]),-1)),[Sales]),[sales])

or measure using time intelligence

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

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

[Anonymous · Accepted solution]

Hi @amitchandak , thank your for youe suggestion , I tried the formula i get the same result Sum all the sales of previous month for all client . 

Do you any idea How to correct it 

best Regards