Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Finding Difference/Average between Aggregates

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!

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.