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.
I have this table with Date, State and CountState. I want to know how to calculate the cummulative values in the "Incremeatal Values Date wise" based on the "State" so that I can prepare the graph as shown below:
Date | State | CountState | Incremental Values Date wise |
18-Feb-19 | Active | 2 | 2 |
19-Feb-19 | Active | 1 | 3 |
07-Feb-19 | Active | 2 | 5 |
14-Feb-19 | Active | 1 | 6 |
10-Jan-19 | Active | 1 | 7 |
31-Jan-19 | Active | 1 | 2 |
04-Jan-18 | Closed | 1 | 1 |
03-Nov-17 | Closed | 1 | 2 |
23-Oct-17 | Closed | 1 | 3 |
12-Dec-17 | Closed | 1 | 4 |
07-Nov-17 | Closed | 1 | 5 |
26-Feb-18 | Closed | 1 | 6 |
17-Nov-17 | Closed | 5 | 11 |
02-Nov-17 | Closed | 1 | 12 |
31-Oct-17 | Closed | 3 | 15 |
19-Oct-17 | Closed | 2 | 17 |
04-Dec-17 | Closed | 1 | 18 |
05-Dec-17 | Closed | 1 | 19 |
Solved! Go to Solution.
Hi @PattemManohar
I checked your dataset. I think you were wrong at the line 10th. because the state is active and Project is Project B it means we run across it for first time it should be 1 for Running total by state & Project column.
Please create a new column like that
@sprasad The expected column name was contradicting with the requirement statement. That's why, I'm provding two options.
1. If you want the Running Total based on State by Date Wise. Add a New Column as below
RunningTotalByStateDate = VAR _CurrentCount = Test211RunningTotal[CountState] VAR _PreviousCount = CALCULATE(SUM(Test211RunningTotal[CountState]),FILTER(ALL(Test211RunningTotal),Test211RunningTotal[Date]<=EARLIER(Test211RunningTotal[Date]) && Test211RunningTotal[State] = EARLIER(Test211RunningTotal[State]))) RETURN _PreviousCount
2. If you want the Running Total based on State itself without any order (based on the default order of the data present). Then add an Index column in Power Query Editor and add a new column in Modeling Tab (DAX) as below
RunningTotalByState = VAR _CurrentCount = Test211RunningTotal[CountState] VAR _PreviousCount = CALCULATE(SUM(Test211RunningTotal[CountState]),FILTER(ALL(Test211RunningTotal),Test211RunningTotal[Index]<=EARLIER(Test211RunningTotal[Index]) && Test211RunningTotal[State] = EARLIER(Test211RunningTotal[State]))) RETURN _PreviousCount
Proud to be a PBI Community Champion
I think I sent the wrong table.
Now if you can see we have the State Column having the value as New, Closed and Active. Project has value as project A, Project M, project B. So I want the "Running Total by State & Project"
MergedDate - Copy | State | Project | CountState | Running Total By State & Project |
28-Sep-17 | New | Project A | 1 | 1 |
23-Oct-17 | Closed | Project M | 1 | 1 |
26-Jan-18 | Closed | Project M | 1 | 2 |
29-Jan-18 | Closed | Project A | 2 | 2 |
30-Jan-18 | Closed | Project A | 4 | 6 |
30-Jan-18 | New | Project B | 1 | 1 |
31-Jan-18 | Closed | Project A | 6 | 12 |
31-Jan-18 | Closed | Project M | 1 | 3 |
06-Feb-18 | Closed | Project A | 7 | 19 |
07-Feb-18 | Active | Project B | 1 | 2 |
18-May-18 | Closed | Project R | 1 |
Is it possible, pls help me to do this.
Hi @PattemManohar
I checked your dataset. I think you were wrong at the line 10th. because the state is active and Project is Project B it means we run across it for first time it should be 1 for Running total by state & Project column.
Please create a new column like that
Can't we do this calculation in the query table by creating a custom column itself? I want to use other columns in the tquery table to display the details. So if everything is available in the single table then it is easy for me
@Anonymous It was based on the sample test data that was provided initially, but now you have different structure of data. So the same solution will not work for that. Anyway, I hope now you have solved that.
Proud to be a PBI Community Champion
Hi @sprasad your date column is irregular. It should be have a sort logic Ascending or descending. I think we were unable to do that in this sort.
I suppose it would be descending sort if it's okey for you.
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |