Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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!
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!
Anyone else with any ideas on this one?
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!
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?
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!
Have updated with your suggestion and still cannot get this to work? Again unless I have made a mistake on my end?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
103 | |
75 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |