Countdown of hours spent (straight line)

[Mariann]

Hi,
I need to show a countdown of hours spend on a project - over time.

This is my data. Hrs_left is calculated in excel, but this it the calculation I would like to make in PowerBi

Header	Start_date	End_date	Budget_hrs	Duration_day	hrs_pr_day	Project_periode	Hrs_left	Hrs_spent
110170000	06-10-2022	01-04-2024	16300	543	30,02	06-10-2022	16269,98	30,02
110170000	06-10-2022	01-04-2024	16300	543	30,02	07-10-2022	16239,96	60,04
110170000	06-10-2022	01-04-2024	16300	543	30,02	08-10-2022	16209,94	90,06
110170000	06-10-2022	01-04-2024	16300	543	30,02	09-10-2022	16179,92	120,08
110170000	06-10-2022	01-04-2024	16300	543	30,02	10-10-2022	16149,9	150,1
110170000	06-10-2022	01-04-2024	16300	543	30,02	11-10-2022	16119,88	180,12
110170000	06-10-2022	01-04-2024	16300	543	30,02	12-10-2022	16089,86	210,14
110170000	06-10-2022	01-04-2024	16300	543	30,02	13-10-2022	16059,84	240,16
110170000	06-10-2022	01-04-2024	16300	543	30,02	14-10-2022	16029,82	270,18
110170000	06-10-2022	01-04-2024	16300	543	30,02	15-10-2022	15999,8	300,2
110170000	06-10-2022	01-04-2024	16300	543	30,02	16-10-2022	15969,78	330,22
110170000	06-10-2022	01-04-2024	16300	543	30,02	17-10-2022	15939,76	360,24
110170000	06-10-2022	01-04-2024	16300	543	30,02	18-10-2022	15909,74	390,26
110170000	06-10-2022	01-04-2024	16300	543	30,02	19-10-2022	15879,72	420,28
110170000	06-10-2022	01-04-2024	16300	543	30,02	20-10-2022	15849,7	450,3

I need to show something like this - so it will be a countdown of budget hours

Of course, i need these data to be accompagnied by actual spend hours, but this is not a problem - i just can't figure out how to make this "straight line countdown". 

Best regards,
Mariann

[rohit_singh]

Hi @Mariann ,

This is pretty straightforward using DAX. 

1. Load your dataset into the data view. I have named the table 'Project'

2. First you will need to calculate the rank of each day within a given project. This can be done by using the RANKX function and adding a calculated column as follows :

Rank =

    RANKX(

        FILTER(

            Project,

            Project[Header] = EARLIER(Project[Header])

        ),

        Project[Project_period],

        ,

        ASC

        ,

        Dense

    )

This will give you a ranking based on each day of the project. 

3. Based on the rank calculated above, create another calculated column 'Hrs_spent ' that will give you the hours spent. This will be a running total of hours spent in the project by day.

Hrs_spent =

    CALCULATE(

        SUM(Project[hrs_pr_day]),

        FILTER(

            ALLEXCEPT(Project, Project[Header]),

            Project[Rank] <= EARLIER(Project[Rank])

        )

    )

4. Add another calculated column 'Hrs_Left' which is simply 

Project[Budget_hrs] - Project[Hrs_spent]

Now that you have performed all necessary calculations, open the report view and add a line chart visual. Add project_period on the X-Axis and Hrs_left on the Y-Axis and you should see the desired visual.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂