From a Date and Time value, aggregate all records over the following hour

Question

I have an SSAS Tabular data model that consists of a Fact and Dimension table structure. Within this model there is a Date dimension and a Time dimension, that are referenced by each Fact table.

I would like to aggregate Fact data from one table based on a timespan of 1 hour after the specific row in a different Fact table, using a DAX Measure.

The model looks something like this:

DimDate                    
+---------+------------+   
| DateKey | DateValue  |   
+---------+------------+   
|       0 | 2019-01-01 |   
|       1 | 2019-01-02 |   
+---------+------------+   


DimTime
+---------+-----------+
| TimeKey | TimeValue |
+---------+-----------+
|       0 | 00:25     |
|       1 | 12:05     |
|       2 | 12:15     |
|       3 | 12:30     |
|       4 | 13:00     |
|       5 | 17:20     |        
|       6 | 17:50     |        
|       7 | 19:35     |        
|       8 | 19:45     |        
|       9 | 23:50     |        
|      10 | 23:55     |        
+---------+-----------+             

FactEvent
+---------+---------+-----------------+
| DateKey | TimeKey |      Type       |
+---------+---------+-----------------+
|       0 |       1 | Aggregate       | (2019-01-01 12:05)
|       0 |       5 | Aggregate       | (2019-01-01 17:20)
|       0 |       7 | Don't Aggregate | (2019-01-01 19:35)
|       0 |       9 | Aggregate       | (2019-01-01 23:50)
+---------+---------+-----------------+


FactToAggregate
+---------+---------+-------+
| DateKey | TimeKey | Value |
+---------+---------+-------+
|       0 |       2 |     3 | (2019-01-01 12:15)
|       0 |       3 |     7 | (2019-01-01 12:30)
|       0 |       4 |     5 | (2019-01-01 13:00)
|       0 |       6 |     2 | (2019-01-01 17:50)
|       0 |       8 |     4 | (2019-01-01 19:45)
|       0 |      10 |     4 | (2019-01-01 23:55)
|       1 |       0 |     4 | (2019-01-02 00:25)
+---------+---------+-------+

And the results I am after would be:

                     (Filtered)
+---------+---------+-----------+------------------+------------------+-----+
| DateKey | TimeKey |   Type    |   DateTimeFrom   |    DateTimeTo    | Sum |
+---------+---------+-----------+------------------+------------------+-----+
|       0 |       1 | Aggregate | 2019-01-01 12:05 | 2019-01-01 13:05 |  15 | (Sum of FactToAggregate row 1, 2, 3)
|       0 |       5 | Aggregate | 2019-01-01 17:20 | 2019-01-01 18:20 |   2 | (Sum of FactToAggregate row 4)
|       0 |       9 | Aggregate | 2019-01-01 23:50 | 2019-01-02 00:50 |   8 | (Sum of FactToAggregate row 6, 7)
+---------+---------+-----------+------------------+------------------+-----+

I am able to create a table with DAX that returns the appropriate DateTimeFrom and DateTimeTo values using addcolumns, though these columns can't subsequently be used in a filter to then return the correct filter context for a calculate, throwing an error:

Column 'DateTimeFrom' cannot be found or may not be used in this expression.

So how can I use the appropriate hour long timeframe as an aggregation filter to return the relative sum?

@RyanB. It was simply an addcolumns around a filtered summarize that added the DateValue and TimeValue together for the DateTimeFrom and then added an hour to that for the DateTimeTo. As these so-called Extension Columns do not carry any filter context with them however, that route was a bust. I am currently looking at using treatas, though this isn't exactly performant. — iamdave

JBfreefolks JBfreefolks · Accepted Answer · 2019-09-30T14:37:11

I propose this solution :

Here is the model :

Zoom on DateTimeTable :

DateTimeTable = GENERATESERIES(DATE(2019;01;01);DATE(2019;01;02);(1/(24*60)))

Then you set Day; Month and Year if needed...

Zoom on FactEvents :

DateTimeFrom = RELATED('Date'[ DateValue])+RELATED('Time'[TimeValue])
DateTimeTo = [DateTimeFrom]+Time(1;0;0)

Zoom on FactToAggregate :

FTADateTime = RELATED('Date'[ DateValue])+RELATED('Time'[TimeValue])

Then the measure :

Aggregation = 
// Retrieve the Values from the current Row of FactEvent
VAR VrMinDateTime = VALUES(FactEvent[DateTimeFrom]) 
VAR VrMaxDateTime = VALUES(FactEvent[DateTimeTo])
RETURN
// Then Filter the FactToAggregate table wrap in a "if" to avoid total row level generating an error
IF(
    HASONEVALUE(FactEvent[DateTimeFrom]);
    CALCULATE( 
        Sum(FactToAggregate[ Value ]);
        FILTER(
            ALL(FactToAggregate);
            FactToAggregate[FTADateTime]>VrMinDateTime
            &&
            FactToAggregate[FTADateTime]<VrMaxDateTime
        )
    );
    BLANK()
)

Then tou can see the result :

From a Date and Time value, aggregate all records over the following hour

1 Answers