Forum Discussion
Get accumulated pending tasks
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 )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 - _closing2. 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
6 Replies
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 - _closing2. 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
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
Community Champion
Hi Anonymous
why are using TREATAS? Aren't there a relationship between your table and the Calendar table?
- tamerj1
Can you share a screenshot of your data model with the relationships?
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 )
