You were pretty close - the trick to getting [CurrentDate] right is the use of the ALL() function. This handy function overrides any filter conditions you have.
The row context alters the output of [CurrentDate]. Not what we want. So you need to override the filter condition, like this.
[CurrentDate] = CALCULATE(LASTDATE(TransactionTable[Posting Date])
,ALL(TransactionTable)
)
Then you need FILTER()
Then to sum the amount for [CurrentDay] we do this:
[SumAmountCurrentDay] = CALCULATE([SumAmount]
,FILTER(TransactionTable
,TransactionTable[Posting Date]=[CurrentDay]
)
)
We need to use FILTER() because it's a more complicated criteria than CALCULATE can handle by default. FILTER() explicitly tells CALCULATE() which table it needs to filter on - although it might be obvious to us, it isn't to PowerPivot.
Here are the rest of the formulas you need, of varying complexity but mostly reusing functions you've listed above, plus ALL() and FILTER().
[FirstDayOfCurrentMonth]
=CALCULATE(STARTOFMONTH(TransactionTable[Posting Date])
,ALL(TransactionTable)
,FILTER(TransactionTable
,TransactionTable[Posting Date]=[CurrentDay]
)
)
[SumAmountCurrentMonth]
=CALCULATE([SumAmount]
,DATESBETWEEN(DateTable[Date]
,[FirstDayOfCurrentMonth]
,[CurrentDay]
)
)
[FirstDayOfPrevMonth]
=CALCULATE(STARTOFMONTH(TransactionTable[Posting Date])
,ALL(TransactionTable)
,FILTER(TransactionTable
,TransactionTable[Posting Date]=
CALCULATE(dateadd(LASTDATE(TransactionTable[Posting Date])
,-1
,month
)
,ALL(TransactionTable)
)
)
)
[LastDayOfPrevMonth]
=CALCULATE(ENDOFMONTH(TransactionTable[Posting Date])
,ALL(TransactionTable)
,FILTER(TransactionTable
,TransactionTable[Posting Date]=
CALCULATE(dateadd(LASTDATE(TransactionTable[Posting Date])
,-1
,month
)
,ALL(TransactionTable)
)
)
)
SumAmountPrevMonth
=CALCULATE([SumAmount]
,DATESBETWEEN(DateTable[Date]
,[FirstDayOfPrevMonth]
,[LastDayOfPrevMonth]
)
)