Cumulative count with status

[PowerbiLearnuk]

Hi, 

I am new here but I have been a user of the forums for long time. Hats off to all the lovely people out there who is helping others with the answers.

Here my question: I have requirement with cumulative count of approved connectors by different approval status and by quarter which should show only last 4 quarters inlcuding the present quarter if there is no data for any quarter it has to take value of previous quarter and present that. 

I have connectors table like below

connectorid	createddate	approvaldate	approvalstatus
1	30/09/2016	20/06/2022	niche
2	18/7/2017	10/07/2022	standard
3	10/10/2016	10/07/2022	standard
4	10/03/2018	28/08/2022	standard
5	10/10/2016	30/10/2022	standard
6	10/03/2018	01/02/2023	niche
7	21/05/2019	10/03/2023	standard

Expected result is 

Quarter	Status	Totals for Status	Cumulative for quarters
Q3-2022	niche	1	4
Q3-2022	standard	3	4
Q4-2022	niche	1	5
Q4-2022	standard	4	5
Q1-2023	niche	2	7
Q1-2023	standard	5	7
Q2-2023	niche	no data - copy from previous quarter	no data - copy from previous quarter
Q2-2023	standard	no data - copy from previous quarter	no data - copy from previous quarter

If i get the data like this above then my Line and stacked column chart will give 

Q3-2022 - column bar with 4 with a division of 1 and 3 niche and standard

Q4-2022 - column bar with 5 with a division of 1 and 4 niche and standard

I have done the measure for total status = COUNT(Connectors[Status])

Total Cumulative Connectors = CALCULATE(

                                                        Connectors[total status],

                                                        FILTER(

                                                               ALL(Connectors[approvaldate]),

                                                               Connectors[approvaldate]<= MAX(Connectors[approvaldate])
                                                         )
using the above 2 measures, its giving same for total status and Total Cumulative Connectors.
also it doesn't give me like the table which I am expecting above. it gives

Quarter    Status          Total status   Total Cumulative Connectors 

Q3-2022   niche           1                    1

Q3-2022   standard     3                     3

Q4-2022   standard     1                     1

Q1-2023   niche           1                     1

Q1-2023   standard      1                     1

Q2-2023     

Sorry about the long post, it will be great, if anyone can help me getting the desired result. These are all made up numbers, as I can't share the data because of confidentiality.  Thank you.

[PowerbiLearnuk]

Hello all, any help here is very much appreciated. Thank you very much. 

[EricVieira]

Hey buddy, I have a big question with a big answer. Jokes aside, here it is.😂

To create a DAX measure that calculates a cumulative count of approved connectors by different approval status and by quarter, and shows only the last 4 quarters, including the current quarter, while taking the value of the previous quarter if there is no data for any quarter, you can use the following DAX formula:

Approved Connectors Cumulative Count =
VAR QuarterEndDate =
IF(
NOT(ISFILTERED('DateTable'[Quarter])),
MAX('DateTable'[Date]),
MAX('DateTable'[QuarterEnd])
)
VAR QuartersToInclude = CALCULATETABLE(
VALUES('DateTable'[Quarter]),
'DateTable'[QuarterEnd] <= QuarterEndDate,
'DateTable'[QuarterEnd] >= QuarterEndDate - 364
)
VAR StatusValues = VALUES('ConnectorTable'[ApprovalStatus])
RETURN
SUMX(
QuartersToInclude,
VAR QuarterEndDate = 'DateTable'[QuarterEnd]
RETURN
IF(
ISBLANK(
CALCULATE(
DISTINCTCOUNT('ConnectorTable'[ConnectorID]),
FILTER(
'ConnectorTable',
'ConnectorTable'[ApprovalStatus] IN StatusValues
&& 'ConnectorTable'[ApprovalDate] <= QuarterEndDate
)
)
),
IF(
QuarterEndDate = MIN('DateTable'[QuarterEnd]),
0,
[Approved Connectors Cumulative Count]
),
CALCULATE(
DISTINCTCOUNT('ConnectorTable'[ConnectorID]),
FILTER(
'ConnectorTable',
'ConnectorTable'[ApprovalStatus] IN StatusValues
&& 'ConnectorTable'[ApprovalDate] <= QuarterEndDate
)
)
)
)

[PowerbiLearnuk]

Hi Eric, 😂 thanks for the long answer. I have tried this, where the line is VAR QuarterEndDate = 'DateTable'[QuarterEnd] its showing the error and it says 'DateTable[QuarterEnd] does not exist, so, I have changed it to VAR QuarterEndDate = MAX( 'DateTable'[QuarterEnd]) and its stopped showing the error. The result is giving all the statuses for every quarter which is good. however the cumulative is not working its giving the values from that specific quarter only, its not adding the values from the previous quarter when the numbers are there and when numbers are not there as well. so, apart from cumulative other things are working. Thank you.