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.
Thank you first for the feedback on my questions.
Now, unfortunately, I have another issue regarding DATESBETWEEN(
I have a table with hours bookings:
tblHours
Date / Pers. number / Project / Hours
01.01.2020 / 123 / 112200 / 3
01.01.2020 / 123 / 112200 / 3,75
01.01.2020 / 244 / 112200 / 3
05.10.2022 / 123 / 112200 / 3
Now I would like to take a start date from another table for my calculation. This is constantly adjusted due to project shifts.
tblStartdate
Project / Start date
112200 / 24.12.2019
113255 / 01.12.2019
For the calculation I want to determine the number of hours between start date and TODAY().
Measure total hours for project 112200 between 12/24/2019 to TODAY() = 6.75 hours.
CALCULATE(SUM(tblHours[Hours],DATESBETWEEN(tblHours[Date].Date, STARTDATE[DATE], TODAY()
does not work like this.
Thank you very much. This is exactly what I was looking for.
Could I get some more assistance.
I have created a table with all dates from 2010 - 2030 as output.
Now I have again the table hours analog listed above.
For the chart I need the cumulative hours. In the table hours currently listed all booked hours for ALL projects. In my report I select one project at a time with a slicer.
Now I have added a new column "Hours" to my table Date. There are now per date ALL hours for all projects per day listed. Now I have to calculate the cumulative hours.
First, my table must be selected by slicer and I must calculate the cumulative hours.
I hope you understand my request.
Thank you very much for your help. I will test it as soon as possible.
Is there another way to add a "milestone bar" to a timeline.
With the above calculation I want the values from a certain point in time until today.
I would also like to have a graph with a dates series in the X axis. Now I would like to make the graph recognizable with the above starting point.
Hi @MM1984 ,
Try the custom visual Pulse chart, it is designed for showing milestones on a timeline.
Pulse chart: https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-pulse-chart/
Best Regards
Lucien
Hi @MM1984 ,
Create the following column on tblhours:
startdate = LOOKUPVALUE(tblStartdate[Start date],tblStartdate[Project],tblHours[Project])
Column = CALCULATE(SUM(tblHours[Hours]),FILTER(ALL(tblHours),tblHours[Project]=EARLIER(tblHours[Project])&& tblHours[Date]>tblHours[startdate]&&tblHours[Date]<=TODAY()))
And the info you provided ,all three circled below are satisfied, so the value should be 9.75:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi,
You can achieve this kind of filtering e.g. like this:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |