Archive

Archive for April, 2012

DAX – PARALLELPERIOD

April 27, 2012 Leave a comment

In any business domain, it is a common scenario to have tracking metrics to business progress i.e. technically comparison, variance and percentages.
We could solve a metrics scenario using DAX – Parallelperiod function and I think it covered detail explanation as well.

define 
measure 
'Internet Sales'[PresentYearInternetSales] = 
calculate(sum('Internet Sales'[Sales Amount]))

measure 
'Internet Sales'[PrevSales] = 
calculate(sum('Internet Sales'[Sales Amount]),parallelperiod('Date'[Date], -1, year))

measure 
'Internet Sales'[Variance] = 
'Internet Sales'[PresentYearInternetSales] - 'Internet Sales'[PrevSales]

measure 
'Internet Sales'[PercentVariance] = 
IF( ISBLANK('Internet Sales'[PrevSales]),BLANK(),('Internet Sales'[Variance]/'Internet Sales'[PrevSales]))

EVALUATE (
SUMMARIZE ( 'Internet Sales'
,'Date'[Calendar Year]
,"PresentYearInternetSales"
,format('Internet Sales'[PresentYearInternetSales],"currency")
,"PrevYearInternetSales"
, format('Internet Sales'[PrevSales],"currency")
,"Variance"
,format('Internet Sales'[Variance],"currency")
,"PercentageVariance"
,format('Internet Sales'[PercentVariance],"percent")
) 
)

Advertisements
Categories: DAX Tags: