COMPARISON - Current Period sum Vs Previous Period sum % Change

[rpattan]

Hello,

My request to community to look into the DAX requirement for the below scenario.

Story Line: There are "Badges" which are given by employees to co-employees those who helped in their daily work acivities. And the total number of badges will be counted at the end of every month. And this will be measured as performance apprisal at the end of year. There is no limit in giving badges. In this case an employee may recieve more or less and at some time zero.

Requirement: When we SUM up the Badges count, then the;

1. Monthly periods should compare with the previous month.

2. Quarterly periods should compare with the previous quarter.

3. Yearly periods should compare with the previous year.

4. Quarter-to-date periods should compare with the previous quarter.

5. Year-to-date periods should compare with the previous year.

6. Custom date ranges shouldn’t compare with a previous period.

Please note that the data comparison is with perevious period and not with sameperiod last year. 

My Data: (Note: 1). New Date (bin) column created to match with Dimension Date Table. 2). And a COUNT column also created for SUM purpose.)

Dimension Date Table:  (Note: The highlighted Date column is changed to "Date" from "Date/Time" format)                                 

Date Relationship: That I've given      Time Slicer: That i'm Using

   

Data Table Name:  Badges Awarded

Dimension Date Table Name:  Date Table

Link to PBIX file: 

https://drive.google.com/file/d/156PBYM7WEDj6thG9jgTGO4n6TsQ0YR55/view?usp=sharing

..................................................................................................................................................................................................................

I Have tried with different different DAX formulas, which I came to know I'm not good at. And if I paste over here it may mess up and cause confusion to viewers and answer seekers. Believing that this could be a clean source to many visitors, and benefit out of this. 

Please to let me know for more information on this.

MANY MORE THANKS IN ADVANCE.

RK

[OwenAuger · Accepted solution]

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

[Anonymous]

Hi,

I have a list of 5 State Financial years ranging from 2013 to 2018. I am able to calculate difference between two consecutive years(2017,2018) and difference between current year 2018 with any previous year but not able to calculate difference for previous years like (2015,2017) or (2016, 2014)

The DAX functions I used here are: YTD= Total YTD([Value], (Date), “2018/6/30”)
PYTD = Calculate([YTD], Datesbetween(Date), Date(2013,7,1), Date(2017,6,30)))

Can you assist me here? Thanks!

[rpattan]

@OwenAuger

My Gratitude and Appreciation for spending a good amount of time on this query.

This could be a good example to many users like me. Once again THANK YOU Owen.

RK