topic Re: DATEDIFF With Month Filter in DAX Commands and Tips

DATEDIFF With Month Filter

Anonymous — Mon, 20 Jul 2020 13:12:51 GMT

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:

TimeNow = DATEDIFF (DATE (2020,06,01), NOW (), HOUR)

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!

Re: DATEDIFF With Month Filter

mahoneypat — Fri, 17 Jul 2020 22:49:00 GMT

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

Re: DATEDIFF With Month Filter

AntrikshSharma — Sat, 18 Jul 2020 04:58:04 GMT

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

Re: DATEDIFF With Month Filter

Anonymous — Sat, 18 Jul 2020 16:47:49 GMT

It's not clear at all what you want to do... "respect the hour limit of the month" - please define what you mean by this.

Best
D

Re: DATEDIFF With Month Filter

Anonymous — Mon, 20 Jul 2020 13:14:01 GMT

Hello my friend, post edited with the data table example.

Re: DATEDIFF With Month Filter

Anonymous — Mon, 20 Jul 2020 18:04:08 GMT

Hello Antriksh Sharma!

Could you explain me the steps of this calculation?