Weekly Deltas Over Time

0
votes

I am trying to track the difference in license numbers per feature for a tenant. My data is structured like this:

╔══════╦══════════╦══════════╦══════════╗
║ Date ║ Customer ║ Feature1 ║ Feature2 ║
╠══════╬══════════╬══════════╬══════════╣
║ 5/16 ║ A        ║      500 ║      400 ║
║ 5/23 ║ A        ║     1000 ║      300 ║
╚══════╩══════════╩══════════╩══════════╝

I want to be able to see the differences and deltas for Feature1 and Feature2 (and Feature3, etc) for every week. In my example, Customer A's Feature1 has a difference of 500 between 5/16 and 5/23.

Initial thoughts were create a column to calculate previous week for each record and try from there. I understand creating a calculated column for the difference per feature this way would create a circular dependency:

FeatureDelta=CALCULATE(MAX(Table[Feature1]),filter(Table,Table[Date]=earlier(Table[Prv Week]) && Table[Customer]=EARLIER(Table[Customer]))) - CALCULATE(MAX(Table[Feature1]))

There are quite a few features I want to show deltas for. Is there a way I can do this in PowerBI?

1
powerbi

1 Answers

1
votes

Try using MAXX(FILTER(...),...) instead of CALCULATE(MAX(...),...).

Delta1 = 
    VAR PrevDate = MAXX(FILTER(Table2, Table2[Date] < EARLIER(Table2[Date])), Table2[Date])
    RETURN IF(ISBLANK(PrevDate),
               BLANK(),
               Table2[Feature1] -
               SUMX(FILTER(Table2, Table2[Date] = PrevDate), Table2[Feature1]))

You are able to use CALCULATE instead if you remove the row context that's causing the circular dependency.

Delta1 = 
    VAR PrevDate = CALCULATE(MAX(Table2[Date]),
                       ALLEXCEPT(Table2, Table2[Customer]),
                       Table2[Date] < EARLIER(Table2[Date]))
    RETURN IF(ISBLANK(PrevDate),
               BLANK(),
               Table2[Feature1] -
               CALCULATE(SUM(Table2[Feature1]),
                   ALLEXCEPT(Table2, Table2[Customer]),
                   Table2[Date] = PrevDate))

I'm assuming that you want to keep the customer from the row context. If not, you can replace ALLEXCEPT(Table2, Table2[Customer]) with simply ALL(Table2).