Cumulative totals showing blank

[Anonymous]

Hi,

I have the following table Sales with columns 'Sales volume' and 'Year-Month' where Year-Month is 2019-01, 2019-02 etc. and is Text format.

This cannot be changed as this is the way I have been asked to present this.

E.g. 

Sales volume || Year-Month

3000 || 2019-01
2500 || 2019-02
4000 || 2019-03
3500 || 2019-05

Note that if there are no sales in the month e.g. 2019-04 above, the row is left out.

I have the following formula for a measure:

Cumulative Sales = calculate(
sum('Sales'[Sales volume]),
filter(allselected('Sales'[Year-Month]),
ISONORAFTER('Sales'[Year-Month],
max('Sales'[Year-Month]),desc)))

However if no new sales have occured in the month, the measure will show blank for that month, but I would like it to show the total from the previous month if that is the case.

E.g. currently my measure would show

2019-01 : 3000

2019-02: 5500

2019-03: 9500
2019-04:

2019-05: 13000

But I would like it to show

2019-01 : 3000

2019-02: 5500

2019-03: 9500
2019-04: *9500*

2019-05: 13000

Can anyone please help me to change my measure so this will work?

Thank you in advance.

[v-shex-msft · Accepted solution]

HI @Anonymous ,

If '2019-04' not real existed in your table, formula calculation will skip and not calculate on these 'not exist' records.

You can consider to add a calendar table as category and write measure formula to summary sale table amounts based on current date.(calendar not has relationship to sales table)

If above not help, can you please share some sample data for test?

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you @v-shex-msft  I transferred the measure to the calendar table and this is working now.

[MMartin]

Hi Laura,

I try the same as you, and when i put cumulativeSales in the matrix i have seen the result you expect.

First of all, in the year-month column you must have the value of 2019-04, with the blank/null value.

And with your formula:

"CumulativeSales = calculate(

[SumSales];

filter(allselected(Sales[Year-Month]);

ISONORAFTER(Sales[Year-Month];

max(Sales[Year-Month]);desc)))"

The result is:

You have to show the CumulativeSales column.

I hope I've helped.

Best Regards,

MMartin

[Anonymous]

Thanks @MMM  - so does there have to be a null row for 2019-04 in the raw data? As I mentioned in my first post, this isn't the case with my data. Is there a way this can work without the null row? The data is imported from another system so it will be tricky for me to add a null row in.