topic Re: Sum Hours DATESBETWEEN STARTEDATE and Today in DAX Commands and Tips

Sum Hours DATESBETWEEN STARTEDATE and Today

MM1984 — Sat, 29 Jan 2022 13:24:06 GMT

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.

Re: Sum Hours DATESBETWEEN STARTEDATE and Today

ValtteriN — Sat, 29 Jan 2022 14:00:51 GMT

Hi,

You can achieve this kind of filtering e.g. like this:

Filter to today =

var _start=MAX(Aggregation[Opened At (Month)]) return

CALCULATE(SUM(Aggregation[Duration(Secs)]),ALL(Aggregation),Aggregation[Opened At (Month)]>=_start,Aggregation[Opened At (Month)]<=TODAY())

Example:

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/

Re: Sum Hours DATESBETWEEN STARTEDATE and Today

v-luwang-msft — Tue, 08 Feb 2022 03:26:09 GMT

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

Re: Sum Hours DATESBETWEEN STARTEDATE and Today

MM1984 — Tue, 08 Feb 2022 08:05:39 GMT

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.

Re: Sum Hours DATESBETWEEN STARTEDATE and Today

v-luwang-msft — Tue, 08 Feb 2022 08:50:47 GMT

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

Re: Sum Hours DATESBETWEEN STARTEDATE and Today

MM1984 — Tue, 08 Feb 2022 09:47:11 GMT

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.