Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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:

 

*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?

DAX
Histogram
  • smpa01's avatar
    smpa01
    4 years ago

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

     

     

3 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      Thanks!!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.