COMPARISON - Current Period sum Vs Previous Period sum % Change

rpattan

I saw your earlier post on this subject but didn't have a chance to reply.

My suggested approach is:

1. Ensure the relationship between 'Badges Awarded' and 'Date' tables active (it wasn't in the pbix at the link above). 
2. Use the 'Date'[Date] column on your Timeline slicer visual
3. For the Value_PreviousPeriod measure, create something like this:
   

   Value_PreviousPeriod Owen = 
   VAR DateCount =
       COUNTROWS ( 'Date' )
   VAR PeriodType =
       SWITCH (
           TRUE (),
           
           // Complete year selected
           AND (
               HASONEVALUE ( 'Date'[Year] ),
               DateCount = COUNTROWS ( PARALLELPERIOD ( 'Date'[Date], 0, YEAR ) )
           ), "year",
           
           // Complete quarter selected
           AND (
               HASONEVALUE ( 'Date'[YearQuarter] ),
               DateCount = COUNTROWS ( PARALLELPERIOD ( 'Date'[Date], 0, QUARTER ) )
           ), "quarter",
           
           // Complete month selected
           AND (
               HASONEVALUE ( 'Date'[YearMonthnumber] ),
               DateCount = COUNTROWS ( PARALLELPERIOD ( 'Date'[Date], 0, MONTH ) )
           ), "month",
           
           // YTD period selected (takes precedence over QTD)
           AND (
               HASONEVALUE ( 'Date'[Year] ),
               DateCount = COUNTROWS ( DATESYTD ( 'Date'[Date] ) )
           ), "year",
           
           // QTD period selected
           AND (
               HASONEVALUE ( 'Date'[YearQuarter] ),
               DateCount = COUNTROWS ( DATESQTD ( 'Date'[Date] ) )
           ), "quarter"
       )
   RETURN
       SWITCH (
           PeriodType,
           "year", CALCULATE ( [Value], PREVIOUSYEAR ( 'Date'[Date] ) ),
           "quarter", CALCULATE ( [Value], PREVIOUSQUARTER ( 'Date'[Date] ) ),
           "month", CALCULATE ( [Value], PREVIOUSMONTH ( 'Date'[Date] ) )
       )

    

I made the above changes and saved your file here:

PBIX file on OneDrive

The gist of the measure above is to work out what type of date range you have filtered on (PeriodType), by checking if your date selection is the same as a parallel Year/Quarter/Month, or a YTD/QTD period.

Once the PeriodType is determined, this is used to choose how to shift the dates. Note that there are only three possible values for PeriodType since Year/YTD and Quarter/QTD result in the same shift in date filter.

Also, you may want to decide the order of precedence for the different tests, which is represented by the order of the checks in the first SWITCH function call, since for example Jan-Feb could be QTD or YTD.

Regards,

Owen :)