How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI

Scenario:

Suppose, we have a sample Sales data, which is stored with Monthly Snapshot Codes as per below :

Now we would like to compare any of the two Snapshots Data, based on the user selection of the Snapshot_CD.

a) If the user does not make any selection, then by default latest or Current Snapshot will be the Maximum of the Snapshot_CD = 2016_12 ; and Previous Snapshot_CD = 2016_11

b) If the user Selects (Eg: 2016_06) any of the Snapshot_CD, then the Selected Value will be the Current Snapshot and the previous one will be the Previous Snapshot(2016_05).

c) If the user Selects any of the two random Snapshot_CD, then the Maximum of the Selected Value will become a Current Snapshot(Eg: 2016_09) and Minimum of the Selected Value will become a Current Snapshot(Eg: 2016_06).

Now lets Implement the above Scenario, and calculate Sales to compare the Snapshots.

1) First, create a Calculated Column "Snapshot_Rank" using the Dense Rank method on the Snapshot_CD

Snapshot_Rank = RANKX(ds_Sample,[Snapshot_CD],,Desc,Dense)

2) Next, create the Measures that calculate the Current Snapshot and Previous Snapshots based on the Slicer Selection. 

CurSnapshot = IF(ISFILTERED(ds_Sample[Snapshot_CD])=FALSE(),

CALCULATE(MAX(ds_Sample[Snapshot_CD]), ALL(ds_Sample)),

CALCULATE(MAX(ds_Sample[Snapshot_CD]),

                                ALLEXCEPT(ds_Sample,ds_Sample[Snapshot_CD])))

PrevSnapshot =

VAR FilterCheck = ISFILTERED(ds_Sample[Snapshot_CD])

VAR ValMin = CALCULATE(MIN(ds_Sample[Snapshot_CD]),

                                                          ALLEXCEPT(ds_Sample,ds_Sample[Snapshot_CD]))

VAR ValMax = CALCULATE(MAX(ds_Sample[Snapshot_CD]),

                                                            ALLEXCEPT(ds_Sample,ds_Sample[Snapshot_CD]))

VAR ValCheck = (ValMin = ValMax)

VAR PriorRank = LOOKUPVALUE(ds_Sample[Snapshot_Rank],

                                                            ds_Sample[Snapshot_CD], [CurSnapshot])+1

VAR PriorSnapshot = LOOKUPVALUE(ds_Sample[Snapshot_CD],

                                                                  ds_Sample[Snapshot_Rank], PriorRank)

RETURN IF(OR(FilterCheck=FALSE,ValCheck=TRUE()), PriorSnapshot , ValMin)

3) Next, create the Measures that calculate the Sales for the Current Snapshot and Previous Snapshots based on the Slicer Selection. 

Cur Sales = VAR CurVal =[CurSnapshot]

VAR Result = CALCULATE(SUM(ds_Sample[Gross_Sales]),ds_Sample[Snapshot_CD]=CurVal)

RETURN Result

Prev Sales = VAR PrevVal = [PrevSnapshot]

VAR Result = CALCULATE(SUM(ds_Sample[Gross_Sales]),ds_Sample[Snapshot_CD]=PrevVal)

RETURN Result

Cur vs Prev Sales = [Cur Sales]-[Prev Sales]

Sales Growth % = VAR SalesVar = ([Cur Sales] -[Prev Sales])

VAR Result = (SalesVar/[Prev Sales])

RETURN IFERROR(Result,1)

4) Finally, the results of the Sales for Current Snapshot and Previous Snapshots based on the Slicer Selection (No Selection, ByDefault) as per below.

# Results:

a) When user does not make any selection of Snapshot_CD.

b) If the user Selects (Eg: 2016_06) any of the Snapshot_CD, then the Selected Value will be the Current Snapshot and the previous one will be the Previous Snapshot(2016_05).

c) If the user Selects any of the two random Snapshot_CD, then the Maximum of the Selected Value will become a Current Snapshot(Eg: 2016_09) and Minimum of the Selected Value will become a Current Snapshot(Eg: 2016_06).

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------