Forum Discussion
Calculating historic counts based on two date columns
Hi August1987
This seems to be a separate issue to the original question i.e. you now mention a y-axis so there's a chart visual involved.
Have you tried my suggestion and checked my example PBIX file?
It's very hard to diagnose an issue when you don't supply the full dataset, or your PBIX file, or even some screenshots. Please supply your PBIX file if you can, it'll make things much easier to help you.
Phil
Super UserHi August1987
Download example PBIX file with code shown below
Your logic was a bit confusing but I think I've figured it out.
Based on this: Eg. if I filter for 03/11/2025, 17 cases were received before that date, of which 2 are currently outstanding, and 6 have a ClearedDate of either on or after that date so outstanding should be 8.
Then the 17 cases are ID's 14 through 30
The 2 outstanding are ID's 18 and 22
The other 6 are ID's 14, 15, 16, 17, 23, 24.
So filtering on 3 Nov 2025:
Note that a table will show 9 records because that's how many match the selected date
The measure is to calculate the Outstanding value is:
Outstanding =
VAR _Outstanding_Before = CALCULATE(COUNTROWS(Data), FILTER(ALL(Data), 'Data'[ReceivedDate] < SELECTEDVALUE(Data[ReceivedDate]) && ISBLANK('Data'[ClearedDate]) && 'Data'[ClearedFlag] = "N"))
VAR _Cleared_After = CALCULATE(COUNTROWS(Data), FILTER(ALL(Data), 'Data'[ReceivedDate] < SELECTEDVALUE(Data[ReceivedDate]) && 'Data'[ClearedDate] >= SELECTEDVALUE(Data[ReceivedDate]) ))
RETURN _Outstanding_Before + _Cleared_After
Regards
Phil