Forum Discussion
Cumulative count with status
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 ...
Approved Connectors Cumulative Count :=
VAR CurrentQuarterEnd =
MAX('DateTable'[QuarterEnd])
VAR QuartersToInclude =
CALCULATETABLE(
VALUES('DateTable'[QuarterEnd]),
'DateTable'[QuarterEnd] <= CurrentQuarterEnd &&
'DateTable'[QuarterEnd] >= EDATE(CurrentQuarterEnd, -12)
)
VAR StatusValues =
VALUES('ConnectorTable'[ApprovalStatus])
RETURN
SUMX(
QuartersToInclude,
VAR ThisQuarterEnd = [QuarterEnd]
VAR Cumulative =
CALCULATE(
DISTINCTCOUNT('ConnectorTable'[ConnectorID]),
FILTER(
ALL('DateTable'),
'DateTable'[QuarterEnd] <= ThisQuarterEnd &&
'DateTable'[QuarterEnd] IN QuartersToInclude
),
FILTER(
'ConnectorTable',
'ConnectorTable'[ApprovalStatus] IN StatusValues &&
NOT(ISBLANK('ConnectorTable'[ApprovalDate]))
)
)
RETURN Cumulative
)
mudei!!