Alright, I have refined the code a little, based on your values, so for the number of open documents it now looks like this:
Open documents =
VAR _maxDate =
MAX ( vDate[Date] )
VAR _minDate =
MIN ( vDate[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Document Number] );
FILTER (
'Table';
'Table'[Clearing date] > _minDate
&& 'Table'[Document Date] < _maxDate
)
)
+ CALCULATE (
DISTINCTCOUNT ( 'Table'[Document Number] );
FILTER (
'Table';
ISBLANK ( 'Table'[Clearing date] )
&& 'Table'[Document Date] < _maxDate
&& _maxDate <= TODAY ()
)
)A quick walkthrough of the code:
_minDate and _maxDate calculates the min and max date in the the current context. If you choose 1 week it will be the first and the last day of the week. If you choose a whole year, it will be the first and the last date of the year.
Next there is a part of code that starts with calculate. It counts the number of distinct document numbers which satisfy the following criteria:
Clearing date must be a later date than the first date in the current context.
Document date(*) must be an earlier date than the last date in the current context.
The table 'Table' will be filtered be these criterias, and the output will be number of distinct document numbers. With these criterias, when you select a spesific week, you will count all documents that were open at one point in that week. If was cleared on Wednesday, it counts as open that week. The nice thing about this definition is that if you select e.g. a whole year, you will see all documents that were open that year.
The second calculate-part handles documents which has no clearing date yet. This document will be counted according to these criterias:
Document date(*) must be an earlier date than the last date in the current context.
Last date in current context must be earlier or equal to todays date.
Hope this cleared things up a little for you. Here is the link to the power bi file: pbix
