DATEDIFF With Month Filter

[Anonymous]

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:

Spoiler

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!

[Anonymous]

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

[AntrikshSharma]

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

[Anonymous]

Hello Antriksh Sharma!

Could you explain me the steps of this calculation?

[mahoneypat]

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

[Anonymous]

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