Have you ever wondered how to compare this month's data up to the current day of the month to the previous month's data to the same day? An example would be data from June 1st - 5th compared to May 1st - 5th.
The method mentioned in this blog is from this article by Eliot Stam. I added the calculations for previous year data.
To test this dashboard, I recommend modifying the Sample - Superstore data set. I removed December data after the 16th of the month. I also changed the year dates in my version of this data set so it may not match yours.
You will need to create a series of Dimension calculations:
Latest Date
{Fixed : Max([Order Date])}
Latest Month
Date(DATETRUNC('month',[Latest Date]))
Latest Year
Date(DATETRUNC('year',[Latest Date]))
Previous Month
Date(DATEADD('month',-1,[Latest Month]))
Previous Year
Date(DATEADD('year',-1,[Latest Year]))
You will need to create a series of Measure Calculations:
Sales (Previous Month to Date)
If DATETRUNC('month',[Order Date])=DATETRUNC('month',[Previous Month])
AND DATEPART('day',[Order Date])<=DATEPART('day',[Latest Date])
THEN
[Sales]
END
Sales (This Month)
If DATETRUNC('month',[Order Date])=DATETRUNC('month',[Latest Month])
AND DATEPART('day',[Order Date])<=DATEPART('day',[Latest Date])
THEN
[Sales]
END
Sales (Previous Year Current Month)
If DATEPART('year',[Order Date])=DATEPART('year',[Previous Year])
AND
DATEPART('month',[Order Date])=DATEPART('month',[Latest Month])
AND DATEPART('day',[Order Date])<=DATEPART('day',[Latest Date])
THEN
[Sales]
END
Sales (Previous Year Previous Month)
If DATEPART('year',[Order Date])=DATEPART('year',[Previous Year])
AND
DATEPART('month',[Order Date])=DATEPART('month',[Previous Month])
AND DATEPART('day',[Order Date])<=DATEPART('day',[Latest Date])
THEN
[Sales]
END
The above calculations are pretty self-explanatory. Sometimes it is useful especially in retail to figure out where you were the previous year / current month and the previous year / previous month.
I set up the dashboard as follows:
Please notice the Day(Order Date) is set to Discrete.
You should always check your work in another worksheet with the dates filtered to see if they match.
Comments