In the previous post we saw the shortcomings of using ParallelPeriod DAX function when it comes to summarizing data against a different dimension attribute. This problem can be overcome by using the DAX Formula SAMEPERIODLASTYEAR. Notice how this function can retrieve results for the same period of the previous year but won’t be able to traverse dates within the same year for Quarter by Quarter measures for example.
The below screenshot shows a side by side comparisons of ParallelPeriod with its flaw and how that flaw is corrected in SAMEPERIODLASTYEAR.
We can see the totals much up fine for Column 1 and 3 however Column 1 uses ParallelPeriod and is useless when viewing data on month by month basis.
The Formula for using SAMEPERIODLASTYEAR is as shown below
ExSamePeriodLastYear = CALCULATE(SUM('Fact Purchase'[Ordered Quantity]),SAMEPERIODLASTYEAR('Dimension Date'[Date]))There is no SQL equivalent for this Function since SQL you can however write a ROLLUP CUBE query and bucket data by each grouping following by lead and lag as required. E.g. when viewing the data at year level lag by 1, when viewing by month lag by 12 etc.
Please Consider Subscribing
