Return last Sales if current Sales are 0

[Crydus]

Hey, 

I have a problem within Power BI. 

I have a list of sales per month and in some months there are no sales. Now I want to display the last value where the sales have been >0, but i don't know how to do this. 

Here is a small example for you to illustrate the problem. 

Thanks in advance!!

[sokg · Accepted solution]

Hi @Crydus 

First create a measure

SumOfSales = SUM(Table1[sales])

and a second measure

SalesNew =

VAR CurrentDate =

MAX ( Table1[datum] )

VAR maxDateWithSales =

CALCULATE (

MAX ( Table1[datum] );

FILTER (

ALL ( Table1[datum] );

[SumOfSales] > 0

&& Table1[datum] <= CurrentDate

)

)

RETURN

CALCULATE (

[SumOfSales];

FILTER ( ALL ( Table1[datum] ); Table1[datum] = maxDateWithSales )

)

the result is

[Crydus]

Thank you so much. 

I'm fairly new to PBI and the FILTER expressions are still very confusing to me. But this works as intended 🙂 

Again, many, many thanks. 

[sokg]

Glad to help you!!!

[Omega]

Try the below measure: 

Measure = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date]) && SUM('Table'[Sales])>0))

[Crydus]

Thanks for your suggestion, unfortunately this didn't bring me the expected result 😞 

My goal is to have a column where no row is equal to 0, i.e. if February has no sales, I want to have the sales of january and in August the Sales of may should be visible.