Forum Discussion
Cumulative count with status
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
)
)
)
)
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.