Get accumulated pending tasks

[Anonymous]

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:

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

I was helped before and came up to this solution, but it only works for 2021 dates. If I use 2022 data, the calculation doesn't work:

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 )

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Update the formula of your measure as below

Measure = 
VAR _opening =
    CALCULATE (
        DISTINCTCOUNT ( 'demand'[id] ),
        FILTER (
            'demand',
            'demand'[opening date] <= SELECTEDVALUE ( 'Calendar'[Date] )
                && (
                    'demand'[closing date] > SELECTEDVALUE ( 'Calendar'[Date] )
                        || ISBLANK ( 'demand'[closing date] )
                )
        )
    )
VAR _closing =
    CALCULATE (
        DISTINCTCOUNT ( 'demand'[id] ),
        FILTER (
            'demand',
            NOT ( ISBLANK ( 'demand'[closing date] ) )
                && 'demand'[closing date] <= SELECTEDVALUE ( 'Calendar'[Date] )
        )
    )
RETURN
  _opening - _closing

2. Create a column chart as below screenshot (Axis: Date field of Calendar table   Values: [Measure])

In addition, you can refer the following links to get it.

Count open tickets over previous time periods

Total Number Of Staff Over Time - Power BI Insights

Calculating Employee Attrition with DAX

Best Regards

[Anonymous]

Hi @Anonymous , thank you so much for your answer. helped me a lot. I used only the first part of the code with some adjustments and seems that it did the trick:

Pending =
    CALCULATE (
        DISTINCTCOUNT ( 'demand'[id] ),
        FILTER (
            'demand',
            'demand'[opening date] <= MAX( 'Calendar'[Date] )
                && (
                    'demand'[closing date] > MAX( 'Calendar'[Date] )
                        || ISBLANK ( 'demand'[closing date] )
                )
        )
    )

[tamerj1]

Can you share a screenshot of your data model with the relationships?

[Anonymous]

actually there is no relationship between the two tables. 

the DAX formula is this:

Atividades pendentes = 
VAR _filter =
    FILTER (
        ALL ( 'Calendário' ),
        'Calendário'[Ano] <= MAX ( 'Calendário'[Ano] )
            && 'Calendário'[Mês Num] <= MAX ( 'Calendário'[Mês Num])
    )
VAR _opening =
    CALCULATE (
        COUNT ( df[data_inicio] ),
        TREATAS ( VALUES ( 'Calendário'[Date] ), df[data_inicio] )
    )
VAR _closing =
    CALCULATE (
        COUNT ( df[data_conclusao] ),
        TREATAS ( VALUES ( 'Calendário'[Date] ), df[data_conclusao])
    )
VAR _accumulatedOpening =
    CALCULATE (
        CALCULATE (
            COUNT ( df[data_inicio] ),
            TREATAS ( VALUES ( 'Calendário'[Date] ), df[data_inicio])
        ),
        _filter,
        df
    )
VAR _accumulatedClosing =
    CALCULATE (
        CALCULATE (
            COUNT ( df[data_conclusao] ),
            TREATAS ( VALUES ( 'Calendário'[Date] ), df[data_conclusao] )
        ),
        _filter,
        df
    )
VAR _diff = _accumulatedOpening - _accumulatedClosing
RETURN
    IF ( _opening <> BLANK () || _closing <> BLANK (), _diff )

[tamerj1]

Hi @Anonymous 

why are using TREATAS? Aren't there a relationship between your table and the Calendar table? 

[Anonymous]

sincerely, I do not know. I had some problems with this formula and couldn't fully understand it.