Sunday, May 29, 2022

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
--------------------------------------------------------------------------------------------------------

1 comment:

  1. I cannot explain how helpful this was. You are so appreciated for your simple explanation of a tricky data puzzle. This project has taken me weeks and your solution was brilliant! Thank you thank you

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.