cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Running total with filter

I need to do a running total calculation for the below 

 

TaskDue DateActual Completion Date
A11/15/20182/4/2018
A22/16/20183/7/2018
A33/20/20181/15/2018
A44/21/20182/16/2018
A55/23/20189/28/2018
A66/24/201810/30/2018
A77/26/201812/1/2018
A88/27/20189/9/2018
A99/28/2018 
A1010/30/2018 
A1112/1/2018 


Conditions :

1)  Task should be included in the Running total if the Actual completion date>"Due .Date" or the Actual completion date is null 

2) For instance, Task A5 has a  due date as 5/23/2018 but task completed on 9/28/2018 , it should be calculated untill the Sep-2018 as a backlog, whereas Task A3 should not be in the Running total 

 

Thanks 

 

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Running total with filter

Is there a values column?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Regular Visitor

Re: Running total with filter

Didn't understand that. As in?

Highlighted
Super User IV
Super User IV

Re: Running total with filter

Refer this blog to discuss similar stuff :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Running total with filter

Hi,

Show the end result that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Support
Community Support

Re: Running total with filter

Hi @pd_palanisamy ,

 

Please check if this is what you want:

 

1. Assume that all tasks are assigned on 2018/1/1.

 

2. Create a Calendar table.

Calendar = CALENDAR(DATE(2018,1,1),DATE(2018,12,31))

 

3. Create relationship.

rel.PNG

 

4. Create measures like so:

Measure = 
VAR CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR CurrentDueDate =
    MAX ( 'Table'[Due Date] )
VAR CurrentActualDate =
    IF (
        MAX ( 'Table'[Actual Completion Date] ) = BLANK (),
        TODAY (),
        MAX ( 'Table'[Actual Completion Date] )
    )
VAR DD =
    IF (
        CurrentDueDate <= CurrentActualDate
            && CurrentDate <= CurrentDueDate,
        CurrentDueDate,
        IF (
            CurrentDueDate <= CurrentActualDate
                && CurrentDate >= CurrentDueDate
                && CurrentDate <= CurrentActualDate,
            CurrentActualDate,
            IF (
                CurrentDueDate > CurrentActualDate,
                CurrentActualDate,
                IF ( CurrentActualDate = TODAY (), CurrentDate )
            )
        )
    )
VAR Is1 =
    IF ( DD = CurrentDate, 1, IF ( DD <= MAX ( 'Calendar'[Date] ), 1, 0 ) )
RETURN
    Is1
Running total = 
CALCULATE ( COUNT ( 'Table'[Task] ), ALLSELECTED ( 'Table' ) )
    - CALCULATE (
        SUMX ( 'Calendar', [Measure] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )

run.gif

For more details, please check the attached PBIX file.

 

If I understand it wrong, please let me know.

 

 

Best Regards,

Icey

 

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

Highlighted
Community Support
Community Support

Re: Running total with filter

Hi @pd_palanisamy ,

 

Is this problem solved?

 

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.


If not, please let me know.

 


Best Regards
Icey

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors