Hi,
I have a problem with calculating a balance for the previous month.
I have a table with Month-End-Date, accounts and balances and I have a date table containing 1 row for each date and a relationship between the 2 tables so a very simple model.
I have a measure for TotBalance=Sum(Balance)
I have created another measure
TotBalancePreviousMonth = Calculate ([TotBalance], PREVIOUSMONTH(DATETABLE[Date]))
When I create a pivot table containing Month-End-Date, TotBalance and TotBalancePreviousMonth, the previous month balance is blank but I can't see what I have done wrong, can you help?
Thanks, Ian.
I created a simple model based on your description and suspect, despite the fact that you created a calendar/date table, that you populated the pivot table with the end-of-month date field of the Transactions table rather than with the date field of the Calendar table.
The attached file demonstrates the difference. If this doesn't make sense, please come back here and upload a file with some data and a model that clarifies the problem.