top of page

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

Updated: Jun 15, 2021

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

Previous 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.