cancel
Showing results for 
Search instead for 
Did you mean: 
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-1639673005619.png*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?

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

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

 

smpa01_0-1639675244361.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

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

 

smpa01_0-1639675244361.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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.

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

Thanks!!!

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors