sum up to last month

[alexbalazsalex]

Dears,

I would need you support i have the below formula :

calculate(sum('Table'[TONS]),filter(all('Table'[Year_Month]),'Table'[Year_Month]<=max('Table'[Year_Month])))

It is callculating the grand total up to the selected date but not showing all customers that i have up to the selected date when i put it in the matrix next to tons.

Any Ideas ? thans

[hnguy71]

If you wanted to show all customers even when they don't have any data to provide up to max year_month consider using DatesBetween and a date table.

Customer Grand Total = 
var _MinDate = Min([Date])
var _MaxDate = Max([Date])
RETURN
Calculate(Sum([TONS]), DatesBetween([Date Table],_MinDate, _MaxDate))

Hope this points you in the right direction.

[alexbalazsalex]

hi @hnguy71 

Thanks for your reply.

The customer has data up the the max month i selected .

so basicaly i have sales in Jan but the filter is set on Feb and it is calculating the totals up to Feb but not showing the customer that is strange ..

Any ideas ?

[hnguy71]

that's understood. Like I said, using DatesBetween should give you all the underlying data you need. Did you try that out yet?