Most of the action takes place in the MonthlyBalances table:
The essence of this exercise is to determine the prior period balance (which will be zero in the case of the first fiscal month of the year). Then, for P&L accounts, subtract this from the (cumulative) month in question to get the total transactions just for the month. As mentioned, this uses the EARLIER() function thus:
[Prior period YTD amt] =
The thing on the right hand side is the sum of [YTD amt] where the FiscalMonthKey is one less that the one we are looking at (i.e. the prior month), … and we are looking at the same account. If your data has a department, cost center, or similar on each row, you would need an additional filter something like:
For want of a better explanation, the EARLIER() function allows you to find the same record (same account, same department) but for the prior month - hence the "- 1" in the formula above. Once you have that number you subtract it from the current month, in the case of a Flow account, or leave it as is in the case of a Balance account:
=IF(RELATED('ChartOfAccounts'[Flow or Balance])="FLOW",[YTD amt]-[Prior Period YTD Amt],[YTD amt])
In a later post I'll explain how you can use similar logic to do Current-Month-to-Same-Month-in-Prior-Year comparisons, by selecting just the current month in your pivot or cube formula report.
This report shows a selection of balance sheets accounts ("Balance") and P&L account accounts ("Flow") with the original cumulative balance and the calculated monthly balance for P&L accounts.
For the balance sheet accounts the cumulative YTD number and the monthly number are the same. For the P&L accounts the monthly numbers are the difference between the cumulative and the prior month cumulative. July is the first month of the fiscal year in this example so cumlative and monthly numbers are the same. The prior month returns zero because the DAX fomula is looking for FiscalMonthKey 201400 which has no data.
Like many things Excel/Power Pivot/life-in-general there are multiple ways to slice this cat, some probably better than the one I am suggesting above. My intention has been to minimize the number/complexity of DAX functions. Sometimes this is at the expense of adding extra calculated columns which consume resources. However finance and accounting applications modeled in Power Pivot are not commonly resource-intensive so little harm done, I think.