Sum sales for a specific point in time

[LS1700]

Hello,

I have a table with dates and sales. I would like to present on a chart or on a table, the sales by month, however if the month equals the latest month available in the data, then it should display the data from the previous month.

Formula:

Measure = CALCULATE(SUM(Table1[Sales]),FILTER(all(Table1),(Table1[Date])=EOMONTH(MAX(Table1[Date]),IF(CALCULATE(MAX(Table1[Date]))=CALCULATE(MAX(Table1[Date]),ALL(Table1)),-2,-1))+1))

Displaying the measure on a table, this is what it looks like: as you can see the problem is in the first row. it should display 2:

Date              Sales       Measure

7/1/2017        1            (empty)

6/1/2017        2               2

5/1/2017        3               3

4/1/2017        4               4

3/1/2017        5               5

2/1/2017        6               6

1/1/2017        7               7

Does anyone have an idea how to solve this?

thanks

LS

[LS1700 · Accepted solution]

Solution:

Measure = CALCULATE(SUM(Table1[Sales]),FILTER(ALL(Table1[Date]),CALCULATE(max(Table1[Date]))=EOMONTH((MAX(Table1[Date])),IF((MAX(Table1[Date]))=CALCULATE(MAX(Table1[Date]),ALL(Table1)),-2,-1))+1))