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

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.

 

Example of Staff entered.PNG

 

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

Line Graph.PNG 

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!

 

 

3 REPLIES 3
Community Support
Community Support

Re: Line graph and staff numbers

Hi @TheBlacklist ,

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] )
    )

2.PNG

 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.

TheBlacklist
Frequent Visitor

Re: Line graph and staff numbers

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

Community Support
Community Support

Re: Line graph and staff numbers

Hi @TheBlacklist ,

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.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

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

Top Solution Authors