Calculate Function with Accumulated Value and 2 Filters

[ngadiez]

I have a Measure where I want to get the Accumulated Sales

My Formula is

AccumulatedSales =
CALCULATE (
    SUM ( Table[SalesAmount] ),
    FILTER ( Table, Table[YYMM] >= MAX (Table[YYMM])-1 ),
    FILTER ( ALL ( Table[Day] ), 'Table'[Day] <= MAX ( Table[Day] ) )
)

First, my formula is only this

CALCULATE (
    SUM ( Table[SalesAmount] ),
    FILTER ( ALL ( Table[Day] ), 'Table'[Day] <= MAX ( Table[Day] ) )
)

where Day is just a difference between customer subscription date to transaction date. 
Where I can get a line chart with x axis as Day and y-axis the 'Accumulated Sales'.

However I want to add 1 more filter before this filter where the YYMM (year and month of transaction date) is current month and last month.

MAX (Table[YYMM] ) is the current month.

But the accumulation function didn't work anymore. 

[v-caliao-msft]

@ngadiez,

You create a measure and use the filter FILTER ( Table, Table[YYMM] >= MAX (Table[YYMM] ) ), so you want to get a Month to Date cummulative total, right?

Please try the expression below.
AccumulatedSales =
CALCULATE (
    SUM ( Table[SalesAmount] ),
    FILTER ( Table, Table[YYMM] >= MAX (Table[YYMM] ) && 'Table'[Day] <= MAX ( Table[Day] ) )
)Or you could use TOTALMTD Function to get Month to date total, it evalutes the value of the expression for the month to date, in the current context.
https://msdn.microsoft.com/en-us/library/ee634560.aspx

If this is not what you want, please provide us some sample data, so that we can make further analysis.

Regards,

Charlie Liao

[ngadiez]

Hi @v-caliao-msft

Really apologise that I did not explain it properly.

This is the sample Table

No	MemberStartDate	TransactionDate	MemberID	SalesAmount	Day=TransactioDate-MemberStartDate	YYMM
1	05-Apr-15	05-May-17	1	61.8	761	1705
2	14-Mar-16	12-May-17	2	13.0	424	1705
3	05-Apr-15	31-May-17	1	50.9	787	1705
4	07-Aug-15	05-Apr-17	3	53.3	607	1704
5	14-Mar-16	24-Apr-17	2	21.2	406	1704
6	05-Apr-15	17-May-17	1	46.3	773	1705
7	29-Nov-15	05-Mar-17	4	59.3	462	1703
8	16-Nov-15	23-Mar-17	5	37.9	493	1703
9	16-Nov-15	13-May-17	5	19.6	544	1705
10	07-Aug-15	12-Mar-17	3	34.4	583	1703

Basically, I just want to make a chart where the x-axis is the day(transactiondate-startdate) and y-axis the accumulated sales amount until date.

I only need data for current month (May) and last month(April). The table has all sales amount until current month. The first filter is to get current month Max(Table[YYMM]) and Max(Table[YYMM])-1.

So I did FILTER ( Table, Table[YYMM] >= MAX (Table[YYMM]-1 ) )

The second filter is to do an accumulation function. I don't think I can use TotalMTD because that use date as the input where mine is a day(number).

So what I did is FILTER ( ALL ( Table[Day] ), 'Table'[Day] <= MAX ( Table[Day] ) ).

Hence I will get the Accumulated Sales Amount for y-axis and the day for x-axis.