DAX Dynamic Total with Power View

Question

I am attempting to graph, via Power View, the sum of all account balances at various points in time, so that I can see how the total is changing.

I do not have transaction history (add / subtract), only 'Balance' at a specific point in time for each account.

A simplified example of my data is:

Account Date Balance
a111 01 January 2015 100
a111 01 February 2015 150
b222 01 March 2015 200
c333 01 March 2015 300
b222 01 May 2015 150
d444 01 June 2015 400

As far as I can tell, I need to create a measure to generate a dynamic rank per 'Account', ordered by latest date. I should then be able to create a second measure to SUM each account where the rank = 1.

There is a similar example of this issue discussed in the question PowerPivot DAX - Dynamic Ranking Per Group (Min Per Group), however I cannot seem to get this to display how I want on a line graph in Power View.

What I want to display on the line graph is (letting the graph sort out the missing dates):

Date Balance
01 Jan 2015 100 -- Account a111 only
01 Feb 2015 150 -- Account a111 only, but the new value
01 Mar 2015 650 -- Accounts a111 (latest), b222 and c333
01 May 2015 600 -- As above, but account b222 is updated
01 Jun 2015 1000 -- Latest values for all accounts

However, what I am currently seeing is:

Date Balance
01 Jan 2015 100 -- Sum of Balances matching this date
01 Feb 2015 150 -- As above
01 Mar 2015 500 -- As above
01 May 2015 150 -- As above
01 Jun 2015 400 -- As above

The problem, as far as I can tell, is that at each data point in the graph, the 'filter context' is reducing down to only the rows that match the date, whereas I need all rows on on before that date with a 'rank' of 1.

For reference, I am using Excel 2013 and the data is ultimately coming in via Power Query (pulling data from SharePoint), so I can work some magic there if necessary (i.e. generating a numeric 'group id' for use with DAX MAX() function, as that doesn't seem to like strings).

I hope this is not to confusing, and thanks in advance for any help.

Answer 1

The solution to this is to have a separate date dimension table so balance can be independently calculated on a per-month basis without respect to the fact table.

Let's assume you have a date table called Date.

So, first create the base measure

[Total Balance] := SUM( FactTable[Balance] )

Next we calculate the first month in the fact table to use as a floor for looking through our Date table values.

[First Ever Balance Date] := FIRSTDATE( ALL( FactTable[Date] ) )

And now we determine the last date with a balance for the date range between that first month and the current month in context:

[Last Balance Date] :=
LASTNONBLANK (
    DATESBETWEEN ( Date[Date], [First Ever Balance Date], MAX ( Date[Month] ) ),
    [Total Balance]
)

So take account a111 in the month of 5/1/2015. This measure will look in all the dates between 1/1/2015 (our [First Ever Balance Date] ) and 5/1/2015 ( the MAX Date[Month] ) , find the last row with a balance and return the corresponding Date[Date] ( 2/1/2015 ).

This measure will return BLANK for any month before an account's initial balance. Using BLANKS in date filtering doesn't work, so we should replace these BLANK values with the [First Ever Balance Date] to ensure the running balance for that account will still return 0.

[Last Balance Date Remove Blanks] :=
IF (
    ISBLANK ( [Last Balance Date] ),
    [First Ever Balance Date],
    [Last Balance Date]
)

Now we can calculate the current balance of any account for any month by looking up the balance as of the Last Balance Date Remove Blanks

[Last Balance] :=
CALCULATE (
    [Total Balance],
    DATESBETWEEN ( Date[Date], [Last Balance Date Remove Blanks], 
    [Last Balance Date Remove Blanks] )
)

And finally we use a SUMX across the accounts to add up their individual Last Balance amounts.

[Account Total Balance] := SUMX( ALL( FactTable[Account] ), [Last Balance] )

Now in your pivot table you just put Date[Month] on the rows and [Account Total Balance] as the measure. Note this will include months with no fact table rows like 4/1/2015 in your sample set. If you wish to exclude these, add another intermediate measure to count your fact table rows:

[Fact Table Row Count] := COUNTROWS ( FactTable )

And then create a measure to return BLANK() if [Fact Table Row Count] is BLANK(), and otherwise [Account Total Balance]

[Account Balance Hide Rows] :=
IF (
    ISBLANK ( [Fact Table Row Count] ),
    BLANK (),
    [Account Balance]
)