How to find Current Month to Date Data to the Prior Month with the Same Date

Updated: Jun 15

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.


248 views0 comments