Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Line graph and staff numbers

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!

 

 

Need Help

3 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

      • v-xuding-msft's avatar
        v-xuding-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.