Finding Difference/Average between Aggregates

[paulj]

First post here, still a fairly green Power BI user.

I have a database that is basically snapshots of a dataset at varying (manually initiated) intervals.  I'm trying to find a way to present the changes between intervals.  For example on one date I might have 400 widgets of type X and on the next date I could have 500 widgets of Type X.  Since this database is based on snapshots the original 400 widgets from the first date would also be in the second date.  I'd like to be able to present that the change between those two dates was 100.

Also as an extension to that I'm hoping to be able show an average change between intervals for a specified date range.

Hopefully this explanation is clear.  A lot of information I've been reading is based on databases that store transactions or something similarly time-based.  I haven't seen much for databases that are storing snapshots of an entire dataset.

Thanks!

[v-yuta-msft]

@paulj ,

I suppose you have two tables, and your slicer is based on the Date table, you may create a measure using dax below:

Result =
VAR First_Date =
    CALCULATE ( MIN ( Date[Date] ), ALLSELECTED ( Date ) )
VAR Last_Date =
    CALCULATE ( MAX ( Date[Date] ), ALLSELECTED ( Date ) )
VAR Widgets_In_First_Date =
    CALCULATE (
        MIN ( Table[Widgets] ),
        FILTER ( Table, Table[Widgets] = First_Date )
    )
VAR Widgets_In_Last_Date =
    CALCULATE (
        MAX ( Table[Widgets] ),
        FILTER ( Table, Table[Widgets] = Last_Date )
    )
RETURN
    Widgets_In_Last_Date - Widgets_In_First_Date

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paulj]

Thank you for this. Is there anyway to make a sequence of these dates differences?  Assuming my database is updated weekly I'd like to be able to show something like the average widgets added each month.