Calculated Column - Filtering MAX on date range from filter

Question

Consider the following tables - one of printers, the other of page counts from meter readings:

Printers
+------------+---------+--------+
| Printer ID |  Make   | Model  |
+------------+---------+--------+
|          1 | Xerox   | ABC123 |
|          2 | Brother | DEF456 |
|          3 | Xerox   | ABC123 |
+------------+---------+--------+


Meter Read
+-------+------------+-----------+------------+
| Index | Printer ID | Poll Date | Mono Pages |
+-------+------------+-----------+------------+
|     1 |          1 | 1/1/2019  |       1000 |
|     2 |          2 | 1/1/2019  |        800 |
|     3 |          3 | 1/1/2019  |      33000 |
|     4 |          1 | 1/2/2019  |       1100 |
|     5 |          2 | 1/2/2019  |        850 |
|     6 |          3 | 1/2/2019  |      34000 |
|     7 |          1 | 1/3/2019  |       1200 |
|     8 |          2 | 1/3/2019  |        900 |
|     9 |          3 | 1/3/2019  |      35000 |
|    10 |          1 | 1/4/2019  |       1400 |
|    11 |          2 | 1/4/2019  |        950 |
|    12 |          3 | 1/4/2019  |      36000 |
|    13 |          1 | 1/5/2019  |       1800 |
|    14 |          2 | 1/5/2019  |       1000 |
|    15 |          3 | 1/5/2019  |      36500 |
|    16 |          1 | 1/6/2019  |       2000 |
|    17 |          2 | 1/6/2019  |       1050 |
|    18 |          3 | 1/6/2019  |      37500 |
|    19 |          1 | 1/7/2019  |       2100 |
|    20 |          2 | 1/7/2019  |       1100 |
|    21 |          3 | 1/7/2019  |      39000 |
|    22 |          1 | 1/8/2019  |       2200 |
|    23 |          2 | 1/8/2019  |       1150 |
|    24 |          3 | 1/8/2019  |      40000 |
+-------+------------+-----------+------------+

In my Power BI report, I have a Dates table:

Dates = CALENDAR(DATE(2019, 1, 1), DATE(2019, 1, 31))

that I am using as a slicer. The goal is to end up with a delta of Mono Pages during the date range from the slicer. I'm able to grab the difference between each meter read with a fairly complicated calculated column on the Meter Read table:

PagesSinceLastPoll = 
IF(
    ISBLANK(
        LOOKUPVALUE(
            'Meter Read'[Mono Pages],
            'Meter Read'[Index], CALCULATE(
                MAX(
                    'Meter Read'[Index]
                ), FILTER(
                    'Meter Read',
                    'Meter Read'[Index] < EARLIER('Meter Read'[Index])
                        && 'Meter Read'[Printer ID] = EARLIER('Meter Read'[Printer ID] )
                )
            )
        )
    ),
    BLANK(),
    'Meter Read'[Mono Pages] - 
    LOOKUPVALUE(
        'Meter Read'[Mono Pages],
        'Meter Read'[Index], CALCULATE(
            MAX(
                'Meter Read'[Index]
            ), FILTER(
                'Meter Read',
                'Meter Read'[Index] < EARLIER('Meter Read'[Index])
                    && 'Meter Read'[Printer ID] = EARLIER('Meter Read'[Printer ID] )
            )
        )
    )
)

But the performance over 10,000+ rows is pretty bad. I'd like to grab the max and min values for a device in the filtered date range and just subtract instead, but I'm having a hard time getting the right value. My DAX so far keeps getting me the max value from the ENTIRE table, not the table filtered on the dates in my slicer. Everything I've tried so far is some variation on:

MaxInRange =
CALCULATE (
    MAX ( 'Meter Read'[Mono Pages] ),
    FILTER ( 'Meter Read', 'Meter Read'[Printer ID] = Printers[Printer ID] )
)

To summarize: If I have a slicer starting 1/2/2019 and ending 1/5/2019, the max value for Printer ID 1 should read 1800, not 2200.

Thoughts?

Do you have a relationship from your Dates table to your Meter Read table? — Alexis Olson
Yes - One-To-Many, from Dates[Date] to 'Meter Read'[Poll Date] Both columns are of data type Date — Nightglow

Alexis Olson Alexis Olson · Accepted Answer · 2019-02-26T21:48:16

The calculated column can be done more efficiently like this:

PagesSinceLastPoll = 
VAR PrevRow =
    TOPN(1,
        FILTER('Meter Read',
            'Meter Read'[PrinterID] = EARLIER('Meter Read'[PrinterID]) &&
            'Meter Read'[PollDate] < EARLIER('Meter Read'[PollDate])
        ),
        'Meter Read'[PollDate]
    )
RETURN 'Meter Read'[MonoPages] - SELECTCOLUMNS(PrevRow, "Pages", 'Meter Read'[MonoPages])

Using that, the number of pages between two dates can just sum this column on those dates.

If you want to skip that and go straight to a measure, try something like this:

PagesInPeriod = 
VAR StartDate = FIRSTDATE(Dates[Date])
VAR EndDate = LASTDATE(Dates[Date])
RETURN
SUMX(
    VALUES('Meter Read'[PrinterID]),
    CALCULATE(
        MAX('Meter Read'[MonoPages]),
        Dates[Date] = EndDate
    )
    -
    CALCULATE(
        MAX('Meter Read'[MonoPages]),
        Dates[Date] < StartDate
    )
)

Note that if you use Dates[Date] = StartDate, then you'll be off. You want to calculate the max pages before your first included date.

Both of these methods should give the same result:

Calculated Column - Filtering MAX on date range from filter

2 Answers