Forum Discussion
Status Graph with Date Slicer
Hello,
I have data structured as follows:
| Project | Status | Date of Status |
| ProA | started | January 1, 2020 |
| ProA | testing | March 13, 2020 |
| ProA | done | April 2, 2020 |
| ProB | started | February 14, 2020 |
| ProC | started | February 27, 2020 |
| ProC | testing | March 20, 2020 |
To be clear, the date column represents when the project reach the given status.
What I want is a to create a report which has a pie graph and a date slicer. Whatever date the slicer is set to, the pie chart displays the number of projects in each status. So for instance, if the date slicer was chosen as March 15th, the graph would have a value of 2 for started, and 1 for testing.
The main issue I'm having is the inability for pbi to do a count of the latest status of each project. I want pbi to know, for instance, that any time between Feb 27 and March 20, that ProC is in the "started" status, and any time between March 20 and now it is in testing.
Let me know if the problem is clear.
Anonymous I am thinking that you will need a disconnected table of your status codes (use an Enter Data query to enter or use:
New Table = DISTINCT('Table'[Status]). Make sure there are no relationships to this table. Use the column created in this table as your legend. Then do something like the following (below). I mocked up the solution in the attached PBIX below sig. You want Page 18.Measure 18 = VAR __Status = MAX('Table (18a)'[Status]) VAR __Table = ADDCOLUMNS( SUMMARIZE('Table (18)',[Project],"__MaxDate",MAX([Date of Status])), "__Status",MAXX(FILTER('Table (18)',[Date of Status]=[__MaxDate]),[Status]) ) RETURN COUNTROWS(FILTER(__Table,[__Status]=__Status))
3 Replies
Anonymous I am thinking that you will need a disconnected table of your status codes (use an Enter Data query to enter or use:
New Table = DISTINCT('Table'[Status]). Make sure there are no relationships to this table. Use the column created in this table as your legend. Then do something like the following (below). I mocked up the solution in the attached PBIX below sig. You want Page 18.Measure 18 = VAR __Status = MAX('Table (18a)'[Status]) VAR __Table = ADDCOLUMNS( SUMMARIZE('Table (18)',[Project],"__MaxDate",MAX([Date of Status])), "__Status",MAXX(FILTER('Table (18)',[Date of Status]=[__MaxDate]),[Status]) ) RETURN COUNTROWS(FILTER(__Table,[__Status]=__Status))Hello Greg_Deckler , I have a follow up question. You're solution was great, and I want to add something. Besides the pie graph which shows the totals of all the projects, I also want to display a simple table which shows the names of each project, the status, and at what date it got that status. Ideally, I also want the table to be sliced based on status when you click on a piece of the pie graph.
Here is a picture of what I'm going for:
I thought of creating a column in DAX as follows. It is definetely based on what you supplied me before.
LatestStatus = ADDCOLUMNS(SUMMARIZE('DD Statuses Full',[key],"__MaxDate",calculate(MAX('DD Statuses'[StatusCreated]), (filter('DD Statuses Full','DD Statuses Full'[StatusCreated] <= max(DateTable[Date]))))),"__Status",MAXX(FILTER('DD Statuses Full',[StatusCreated]=[__MaxDate]),[Status]))Where DD Statuses Full is my table name, key is the name of the project, StatusCreated is the date that the project got a new status, Status is the status, and DateTable is just a date table that the shown slicer is based off of.The table is not changing when i move the date slicer. Perhaps I have misunderstood how to create tables in DAX, or perhaps I'm using CALCULATE wrong. Any thoughts?