Line graph and staff numbers

[Anonymous]

Hi all,

I'm creating a line graph which is showing serveral measures across the day and how they trend by the hour. 

The labour hours are entered into an Excel spreadsheet showing date, start time and number of staff. This is will only entered twice through the day. For example - 1/05/2019 6am 75 staff.

My Power BI line graph is only showing the number  of staff when they're actually entered. I would like it to show throughout the day.

For example:

7am - 75 staff

8am - 75 staff

9am - 75 staff

10am - 75 staff

11am - 75 staff

12pm - 75 staff

1pm - 75 staff

2pm - 70 staff etc

 

How do I created a Dax that would say take the previous number of staff entered at 6am and continue throughout the day until it's changed?

Thanks in advance for your help!

[v-xuding-msft]

Hi @Anonymous ,

Please create a time table firstly. And then try the measure to check if it works.

Measure =
VAR time1 =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Line] IN FILTERS ( 'Table'[Line] ) )
    )
VAR time2 =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Time] < MIN ( 'Time'[Value] )
                && 'Table'[Line] IN FILTERS ( 'Table'[Line] )
        )
    )
RETURN
    IF (
        MIN ( 'Time'[Value] ) > time1,
        CALCULATE (
            SUM ( 'Table'[Staff] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Line] IN FILTERS ( 'Table'[Line] )
                    && 'Table'[Time] = time2
            )
        ),
        SUM ( 'Table'[Staff] )
    )

 For more details, please see the attachment.

Best Regards,

Xue Ding

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

[Anonymous]

Thank you @v-xuding-msft I wil try this.

[v-xuding-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

Best Regards,

Xue Ding

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