Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |