Forum Discussion
DATEDIFF With Month Filter
Hello my colleagues!
My problem is:
I need to calculate the time difference between dates, starting at 06/01/2020 and ending at "NOW". However, I need this calculation to respect the hour limit of the month, but also be cumulative.
How i'm calculating now:
Currently, my equation is as follows:
The problem in this formula is:
I need every month that passes, those hours accumulate since 06/01/2020, but stop at the end of the month where there was a maintenance register.
My formula will start at June, and the difference in this month is only from 06/01/2020 to 06/30/2020. However for July that is cumulative, from 06/01/2020 to 07/31/2020, For August from 06/01/2020 to 08/31/2020 and so on.
My database have the following data, and the DATEDIFF is what i want to create:
| Equipment | Breakdown Start | Breakdown End | DATEDIFF to NOW (Hours) |
| A | 03/06/2020 09:05:00 | 03/06/2020 15:00:00 | 730 |
| B | 15/06/2020 10:15:00 | 15/06/2020 12:00:00 | 730 |
| C | 08/07/2020 12:00:00 | 08/07/2020 13:00:00 | 1185 (Max is 1460, two months) |
How can i solve this question? Can be in DAX or Query.
Thanks for the help!
5 Replies
- AntrikshSharma
Community Champion
Here is a general pattern for running total calculations:
Running Total = VAR MaxDateInFilterContext = MAX ( Dates[Date] ) VAR MaxYear = YEAR ( MaxDateInFilterContext ) VAR DatesLessThanMaxDate = FILTER ( ALL ( Dates[Date], Dates[Calendar Year Number] ), Dates[Date] <= MaxDateInFilterContext && Dates[Calendar Year Number] = MaxYear ) VAR Result = CALCULATE ( [Total Sales], DatesLessThanMaxDate ) RETURN Result - mahoneypat
Microsoft Employee
What do you mean by "hour limit of the month"? Can you provide example data table and desired output, so a solution can be suggested?
Regards,
Pat
