Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FranciscoHoff
Frequent Visitor

Get accumulated pending tasks

Hi.
I have the following example table:

 

idopening dateclosing date
101/01/202101/02/2021
201/01/202101/02/2021
301/01/202101/02/2021
401/01/202101/02/2021
501/01/2021 
601/01/2021 
701/01/2021 
801/01/2021 
901/02/2021 
1001/02/2021 
1101/02/2021 
1201/02/2021 
1301/02/2021 
1401/02/2021 
1501/02/2021 
1601/02/2021 
1701/03/2021 
1801/03/2021 
1901/03/2021 
2001/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:

 

FranciscoHoff_0-1648489352420.png

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 )
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @FranciscoHoff ,

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])

yingyinr_1-1648712988058.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yiruan-msft
Community Support
Community Support

Hi @FranciscoHoff ,

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])

yingyinr_1-1648712988058.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft , 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
Super User
Super User

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

actually there is no relationship between the two tables. 

 

df_table.png

date_table.png

 

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
Super User
Super User

Hi @FranciscoHoff 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors