Time intelligence for hours rather than days

[pontushaglund]

Hi all.

I have a fact table with hourly measurements with one additional dimension (location). The table is semi additive (it should be summarized over the locations but) and I've successfully used MAXX and VALUES to get accurate Measures in the model.

What I am now trying to solve is the ability to see how many hours in a row that the total value across the locations exceeds a specific value.

Using regular Time Intelligence Functions in DAX this would be easy but since I have DateTime values rather than Date values I'm having some issues. The formulas I am using:

[Sum of MyMeasurement] = SUM('Measurements'[MyMeasurement])

[Sum by datetime] = MAXX(VALUES(DateTime[DateTime]);[Sum of MyMeasurement]) - this works great. It summarizes over my other dimension but looks a the max hourly value regardless of how I group it over month, year, date etc.

[Limit]=15000

[Sum over limit] = IF([Sum by datetime]-[Limit]>0;[Sum by datetime]-[Limit];BLANK()) - this works great as well

[Count of exceedances] = CALCULATE(COUNTROWS(DateTime);FILTER(DateTime;[Sum over limit] > 0 ))

And the one that I have issues with:

[Number of exceedances the last six hours] = 

CALCULATE(

     [Count of exceedances];

     FILTER(

          ALL(DateTime);

          DateTime[DateTime]>=(MAX(DateTime[DateTime])-3/24)

          &&

          DateTime[DateTime]<MAX(DateTime[DateTime])

     )

)

And with issues I mean that even with a filter on a page to show only seven consecutive hours the above measure never displays a results, it just keeps spinning the "progress wheel" in the top left corner of the visual (and maxes out the processor).

My fact table has appr 175000 rows.

All help is appreciated.

[pontushaglund · Accepted solution]

So I managed to solve this and wanted to post the (possible) solution to others that are interested. I started by removing complexity (removing as many other measures as possible) but also changed from countrows to CountX:

[Number of exceedances the last six hours] =
COUNTX(
     FILTER(
          ALL(DateTime);
          [Sum by datetime]>15000 
          &&
          DateTime[DateTime]>(MAX(DateTime[DateTime])-6/24)
          &&
          DateTime[DateTime]<=MAX(DateTime[DateTime])
     );
     DateTime[DateTime]
)