Measure for running total between two dates

[TwiggyHaz]

Hello DAX experts!

This issue seems fairly above my abilities in PowerBI so hoping for some help. I am looking to find the running total of accounts in my data where any accounts are live and not closed by day/month. Currently using Direct Query on 500k rows and additional columns to that shown below. Example of data (Close year of 9999 means account is still live):

ID	Live Date	Close Date	Live Month	Close Month	Stage	Vintage
1	28/05/2019	19/06/2019	May 2019	June 2019	Pre Issue	2010
2	29/11/2018	29/05/2019	November 2018	May 2019	Post Issue	2011
3	08/07/2019	31/07/2019	July 2019	July 2019	Post Judgement	2012
4	17/01/2019	21/02/2019	January 2019	February 2019	Mid Proceedings	2013
5	19/08/2018	12/03/2019	August 2018	March 2019	Post Enforcement	2014
6	18/05/2019	27/06/2019	May 2019	June 2019	Pre Issue	2015
7	14/07/2018	02/06/2019	July 2018	June 2019	Post Issue	2016
8	16/03/2019	01/01/9999	March 2019	January 9999	Post Judgement	2017
9	17/03/2019	28/06/2019	March 2019	June 2019	Mid Proceedings	2018
10	23/10/2018	19/07/2019	October 2018	July 2019	Post Enforcement	2019
11	05/05/2018	27/08/2018	May 2018	August 2018	Pre Issue	2010
12	02/10/2018	29/08/2019	October 2018	August 2019	Post Issue	2011
13	07/05/2019	08/08/2019	May 2019	August 2019	Post Judgement	2012
14	25/07/2018	02/02/2019	July 2018	February 2019	Mid Proceedings	2013
15	21/12/2018	25/04/2019	December 2018	April 2019	Post Enforcement	2014
16	29/04/2018	30/06/2018	April 2018	June 2018	Pre Issue	2015

Ideally I need the solution to show by month how many live accounts there are, and to have this linked back to the dataset so additional columns can be used in slicers so the output changes. I have produced exactly how I would do this in Excel, so I am hopeful this can be done in DAX as well.

Thanks in advance!Example in ExcelBase Data

[TwiggyHaz · Accepted solution]

I initially solved this with a brute force method in SQL, creating a new table with a row for each account for each month live and running a countrows over this. While this worked and allowed interactivity the table itself was massive and would grow by millions of rows each year.

Have just cracked this using DAX:

1. Add a date table to the model, for this example just the date and month name was needed.

2. Create a active relationship from Live Date to the Date column in the date table

3. Create an inactive relationship from Close Date to the Date column in the date table

4. Add the following measure:

Running Live = 
VAR RunningPlacedTotal =
    CALCULATE (
        COUNTA ( 'Base Data'[Live Month] ),
        FILTER (
            ALL ( 'Dates Base'[MonthName] ),
            ISONORAFTER ( 'Dates Base'[MonthName], MAX ( 'Dates Base'[MonthName] ), DESC )
        )
    )
VAR RunningCloseTotal =
    CALCULATE (
        COUNTA ( 'Base Data'[Close Month] ),
        USERELATIONSHIP ( 'Base Data'[Close Date], 'Dates Base'[DateID] ),
        FILTER (
            ALL ( 'Dates Base'[MonthName] ),
            ISONORAFTER ( 'Dates Base'[MonthName], MAX ( 'Dates Base'[MonthName] ), DESC )
        )
    )

RETURN RunningPlacedTotal - RunningCloseTotal

5. Add graph with measure to values, and the date table month to the axis.

6. Add a visual level relative date filter to match your needs.

Hope this helps!

[TwiggyHaz]

Anyone else with any ideas on this one?

[Anonymous]

Sounds like you can avoid having to create a new measure!

Simply create a new chart in Power BI with the Close Month as your x axis. For your values, put in a count of "Live Month". Then to only show live accounts, filter the visual by "Close Month" to only show the January 2099 values. You should be able to recreate the two charts you have in Excel!

Hope this helps!

[TwiggyHaz]

Cheers for the reply! Have attached my results, both by just showing the 9999s and also by excluding them. Can't get the same results unless I have done somthing incorrect?

Just 9999sExcluding 9999s

[Anonymous]

My mistake! Use "Live Month" for your x-axis, and then use count of "Live Month" for your values. This way you are counting the amount of Live Accounts were created in that month. Then place "Close Month" as a visual filter and filter to only show the 2099's, limiting the data to only show live accounts that have not been closed

Hope this works!

[TwiggyHaz]

Have updated with your suggestion and still cannot get this to work? Again unless I have made a mistake on my end?