Cumulative count split by attribute's values

Question

Hello community,I have been trying to find a solution to the following problem. This is the scenario:ProjektReqIDStatusModifiedDateC12345AGREED02.07.2022 00:00:00C112233PARTLY_AGREED03.07.2022 00:00:00C112233NOT_AGREED04.07.2022 00:00:00C112233AGREED05.07.2022 00:00:00C9876AGREED06.07.2022 00:00:00This table contains 3 requirements, one of which is being changed 3 times on consecutive days. I am now trying to calculate the number of ReqIDs cumulated per day, and split by state, using this measure:# of ReqID = CALCULATE( DISTINCTCOUNTNOBLANK('Tabelle1'[ReqID]), FILTER ( ALLSELECTED('Tabelle1'), 'Tabelle1'[ModifiedDate] &lt;= MAX ('Tabelle1'[ModifiedDate]) ))&nbsp;This would be my expected result:&nbsp;total of ReqIDAGREEDPARTLY_AGREEDNOT_AGREED02. Jul11&nbsp;&nbsp;03. Jul211&nbsp;04. Jul21&nbsp;105. Jul22&nbsp;&nbsp;06. Jul33&nbsp;&nbsp;&nbsp;Unfortunately, this is the result:Why am I getting a count of 2 for 3rd and 4th for the states? How can I avoid this?Thanks a lot for your help!Regards,Marc

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
Should be the ALLSELECTED, you can do like this

# of ReqID = 
CALCULATE(COUNTROWS(VALUES(Tabelle1[ReqID])), Tabelle1[ModifiedDate]&lt;=MAX(Tabelle1[ModifiedDate]))
&nbsp;

anonymous · Answer

Thanks a lot for your answer, Vera.There is still something missing though: now the sums in lines don't add up. For 3rd and 4th, there should be a 1 for AGREED, too.(Sorry, I can't get the formatting nice on my "expected results" table.)

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
If the AGREED should have 1 as cumulative, then why the other two do not have? The total still doesn't add up...I had a another date table, did not use the date column in the same table.
&nbsp;
You need to explain your logic

&nbsp;
&nbsp;
&nbsp;

anonymous · Answer

Thanks again for looking into my problem, Vera!So, I got 3 requirements in total, but not from the first day onwards.On 2nd, there is only 1.On 3rd, another one is added (112233). Total count is 2. But 1 requirement is AGREED, the other new one is PARTLY_AGREED.On 4th, the new one (112233) changes its state to NOT_AGREED. Total count is still 2, but now there is still the one from the beginning (AGREED) and now one in NOT_AGREED. So, a "1" is expected to appear twice.On 5th, the requirement 112233 is finally agreed, total count is still 2 and therefore all 2 are AGREED.On 6th another new requirement is added, therefore the total count is 3, and they are all AGREED (=3 in that column).&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
I am not sure how many scenarios you might have, say&nbsp;NOT_AGREED and&nbsp;PARTLY_AGREED will only appear once for each&nbsp;requirement? you need to go through all scenarios...here is one based on your sample only

# of ReqID = 
VAR CurDate=MAX(Dates[Date])
RETURN
IF(SELECTEDVALUE('Tabelle1'[Status])="NOT_AGREED"||SELECTEDVALUE('Tabelle1'[Status])="PARTLY_AGREED",
CALCULATE(COUNTROWS(VALUES(Tabelle1[ReqID])),Tabelle1[ModifiedDate]=CurDate),
CALCULATE(COUNTROWS(VALUES(Tabelle1[ReqID])),Tabelle1[ModifiedDate]&lt;=CurDate))
&nbsp;

Forum Discussion

Cumulative count split by attribute's values

6 Replies

Related content

Cumulative Total

Getting Period values from Cumulative

Creating Cumulative Graphs

Cumulative Sum without repeat value

Cumulative total more than current value using current filters

Projekt	ReqID	Status	ModifiedDate
C	12345	AGREED	02.07.2022 00:00:00
C	112233	PARTLY_AGREED	03.07.2022 00:00:00
C	112233	NOT_AGREED	04.07.2022 00:00:00
C	112233	AGREED	05.07.2022 00:00:00
C	9876	AGREED	06.07.2022 00:00:00

	total of ReqID	AGREED	PARTLY_AGREED	NOT_AGREED
02. Jul	1	1
03. Jul	2	1	1
04. Jul	2	1		1
05. Jul	2	2
06. Jul	3	3