Running total of a measure

[Skalpa]

Dear all,

After a lot of research, I am still struggling with the following:

I have a project with x tasks, each beginning and ending at different dates.

I want to have the running total, updated every day of the project calendar, of the number of days active on the project, i.e.:

Task 1 : start date, 03/02/2023, end date, 06/04/2023

Task 2 : start date, 10/02/2023, end date, 20/03/2023

So, my running total table would be something like this:

03/02 : 1

04/02 : 2

...

10/02 : additionnal task starts, to take into account

...etc, etc

I've managed to calculate through the following measure the total of active days on a given date (i.e. how many tasks are in progress on a given date), where 'Référence' is my project table with tasks, start and end dates, and 'Calendrier' is the calendar I created from the min and max dates of all the tasks:

Cumulated_Active_Task_per_day = CALCULATE(

    COUNTROWS('Référence'),

    FILTER(VALUES('Référence'[Début]), 'Référence'[Début]<=MAX('Calendrier'[Date])),

    FILTER(VALUES('Référence'[Fin]), 'Référence'[Fin]>=MIN('Calendrier'[Date]))

)

This works well.

I naively thought that using a quick 'Running total' measure using this calculated measure would do the trick, but unfortunately it does not, and it just seems to give the same numbers as my intial measure!

This is the 'Running total' measure:

RT in Date =

CALCULATE(

    [Cumulated_Active_Task_per_day],

    FILTER(

        ALLSELECTED('Calendrier'[Date]),

        ISONORAFTER('Calendrier'[Date], MAX('Calendrier'[Date]), DESC)

    )

)

And the result:

Date        Tasks in Progress       Cumulated tasks in progress

03/02                  1                                  1

04/02                  1                                  1

...

I have about tried every single formula and functions I could to no avail.

The only thing that gets me close is using a SUMX function with a DATESMTD, but that only works per month, and is reset (as it should) every month).

Thanks for your precious help!

Pascal.

[Skalpa]

Hi,

Thanks for the reply. I must say I'm too new to PowerBI to totally grasp the concepts shared in the links you gave me, but I have a feeling I'm not finding what I'm looking for in there.

In any case, please find attached my pbix file for further investagitation.

SAMPLE FOR PBI FORUM.pbix

Thank you!

[v-yiruan-msft]

Hi @Skalpa ,

It seems that I have no proper access to your shared file. Could you please grant me the sufficient access to it? Later I will provide you a suitable solution asap once I get the file... In addition, you can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Skalpa]

Hi,

Thanks.

Here are 2 links:

- to Dropbox : https://www.dropbox.com/s/zi95k8ly81gqufu/SAMPLE%20FOR%20PBI%20FORUM.pbix?dl=0

- to Googledrive : https://drive.google.com/file/d/1FqM0JQBbWMuamtgVb7RiXdNzS45b2PAN/view?usp=sharing

Let me know if neither still do not work!

Regards,

Pascal.

[v-yiruan-msft]

Hi @Skalpa ,

I updated your sample pbix file, please find the details in page 2 of  the attachment. 

1. Create a measure as below

Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Project_Reference'[Task] ),
        FILTER (
            ALLSELECTED ( 'Project_Reference' ),
            _seldate >= 'Project_Reference'[Start]
                && _seldate <= 'Project_Reference'[End]
        )
    )

2. Create a line chart with date field of calendar table and new measure just as below screenshot

Best Regards

[Skalpa]

Hi,

Thanks for your help on this first step of my problem, but I already had a measure for the number of tasks per day: I definitely do find your measure clearer then the  one I had devised.

The second part of my problem still remains though : how do I get a running total of that measure? 

Thank you!

Regads,

Pascal.

[amitchandak]

@Skalpa , Approach in one of the two blogs should help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if needed

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

[Skalpa]

Hi,

So I sort of found something that works, I just don't know if it's the most elegant and efficicient solution, or if there is a simpler and more correct way to do it.

So, once I created my measure giving me the number of tasks in progress per day, I duplucated my calendar, and created a new column associating this measure to the calendar.

This basically transforms my measure in a table, allowing me to apply a running total which works.

Again, would gladly get a feedback on this workaround before I definitely close the subject.

Thank you!