Get accumulated pending tasks

[FranciscoHoff]

Hi.
I have the following example table:

id	opening date	closing date
1	01/01/2021	01/02/2021
2	01/01/2021	01/02/2021
3	01/01/2021	01/02/2021
4	01/01/2021	01/02/2021
5	01/01/2021
6	01/01/2021
7	01/01/2021
8	01/01/2021
9	01/02/2021
10	01/02/2021
11	01/02/2021
12	01/02/2021
13	01/02/2021
14	01/02/2021
15	01/02/2021
16	01/02/2021
17	01/03/2021
18	01/03/2021
19	01/03/2021
20	01/03/2021

Each row of the table is a task (demand) that has been generated for someone. The objective is to create a histogram that shows the amount of accumulated open demands. An open demand is one that does not have an closing date, or whose closing date is later than the month bar in the histogram. The result should be something like:

*i know this is not a histogram

Note that 8 demands were generated in January, 8 in February and 4 in March, but 4 were closed in February.

Could someone help me create this chart?

[smpa01 · Accepted solution]

@FranciscoHoff  you can create a measure like this

Measure =
VAR _filter =
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Year] <= MAX ( 'Calendar'[Year] )
            && 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
    )
VAR _opening =
    CALCULATE (
        COUNT ( demand[opening date] ),
        TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
    )
VAR _closing =
    CALCULATE (
        COUNT ( demand[closing date] ),
        TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
    )
VAR _accumulatedOpening =
    CALCULATE (
        CALCULATE (
            COUNT ( demand[opening date] ),
            TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
        ),
        _filter,
        demand
    )
VAR _accumulatedClosing =
    CALCULATE (
        CALCULATE (
            COUNT ( demand[closing date] ),
            TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
        ),
        _filter,
        demand
    )
VAR _diff = _accumulatedOpening - _accumulatedClosing
RETURN
    IF ( _opening <> BLANK () || _closing <> BLANK (), _diff )

[FranciscoHoff]

I know this is not a question directly related to the post, but could you help me understand some details of your code? I'm new to the platform and haven't had much contact with DAX.

1) In the FILTER function of the _filter variable, how does the second part of the filter parameter after "&&" not return a month greater than a month from another year that is not the current context year? For example, if it has already processed all the months of 2020, how does it not return the month of December for all entries for the year 2021?
2) Could the CALCULATE nested in the _accumulatedOpening and _accumulatedClosing variable be replaced by the _opening and _closing variables?
3) Why is the IF clause at the end necessary?

Again, thanks for your help.

[FranciscoHoff]

worked perfectly! The only problem I had was that my original data was in datetime, so I needed to convert it to date.

Thanks!!!