DAX Dynamic Total with Power Performance

Question

I'm using Power View to draw the sum of all account balances at different points in time so that I can see how the amount changes.

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

A simplified example of my data:

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 create a dynamic rating for the “Account”, sorted by the last date. Then I had to create a second rating for each account, where rank = 1.

There is a similar example of this problem discussed in the PowerPivot DAX question - Dynamic ranking per group (min. Per group) , however I seem to be unable to do this. Display how I want on the line graph in Power View.

What I want to display on the line graph is (allow the graph to sort 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 see now 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 reduced to only the rows corresponding to the date, while I need all the rows to be included before this date from "rank" 1.

For reference, I use Excel 2013, and the data ultimately comes through Power Query (pulling data from SharePoint), so I can work on magic if necessary (that is, generate a numeric "group id" for use with DAX MAX (), since this is not like strings).

I hope this does not bother and thanks in advance for any help.

Answer 1

The solution is to have a separate table of date sizes so that the balance can be independently calculated based on the month without taking into account the fact table.

Suppose you have a date table named Date.

So first create a basic measure

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

Then we calculate the first month in the fact table to use as a floor to view our Date table values.

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

And now we define the last date with the balance for the date range between this 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, consider a111 on the month of 5/1/2015. This measure will look in all dates between 1/1/2015 (our [First Ever Balance Date] ) and 5/1/2015 ( MAX Date[Month] ), find the last line with the balance and return the corresponding Date[Date] (2 / 1/2015).

This measure returns BLANK within any month prior to the original account balance. Using BLANKS in date filtering does not work, so we must replace these BLANK values ​​with [First Ever Balance Date] to ensure that the current balance for this account will still be returned.

 [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 at the balance with 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 SUM via accounts to add our individual Last Balance amounts.

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

Now in the pivot table, you simply put Date [Month] in the rows and [Account Total Balance] as a measure. Note that this will include months without rows of a fact table such as 4/1/2015 in your sample set. If you want to exclude them, add another intermediate measure to count the rows of the fact table:

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

And then create a measure to return BLANK () if [Fact table row table] BLANK (), and otherwise [Total account balance]

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