ssas tabular compare current vs. previous versions of data

Question

I've searched and found alot of results describing how to compare current and previous time periods in SSAS Tabular, but what I need is a comparison of another nature, namely versions (i.e not related to time )

Say I have two related entities in my Tabular model: Version and Details (1:n)
Each entry in Details relates to one Version, contains a Detail_ID, some descriptive attributes and measures. I want to provide users the following, e.g. in an Excel Pivot.

Filters:
Current Version --> user selects any value from Version
Previous Version --> user selects any value from Version

Rows:
Any descriptive attributes from Details

Columns:
Current (measure) Amount
Previous (measure) Amount
Difference Amount

For the measures, there should be a Current (measure) Amount and a Previous (measure) Amount, and a measure which calculates the difference between the Current and Previous amount for a particular Detail_ID. If the Detail_ID exists in Current Version but not in Previous Version, the difference should be equal to the Amount in Current Version (N - 0 = N)

Can this be done using DAX? Or do I need to remodel by providing additional entities to take care of the current and previous datasets?

Marcus Marcus · Accepted Answer · 2018-03-14T17:04:26

I have done something similar in powerpivot using a disconnected slicer, e.g. the table the slicer was based on had no relationship to the fact table.

The idea being that a user would select their start and end dates from the slicers, and see what changed between versions. The example I am showing below being from something I did for work which we use for tracking promotional changes.

The idea being the calculation does the following:

First it checks if one values has been selected in the slicer, if not it goes to the default case in this example being an empty result.

If the slicer only has one value, we use a calculate statement which filters on the value pulled from the slicer. Which in this case is my snapshot date, but it could be version number or a host of other things.

Snapshot_Start_Fixed:=IF( HASONEVALUE( 'Start_Date'[Start_Date] ),
    CALCULATE(
        SUM('History'[Fixed Spend $]),
        'History'[Snapshot_Date] = VALUES( Start_Date[Start_Date])
    ),
    BLANK()
)

ssas tabular compare current vs. previous versions of data

1 Answers