Month Over Month not working on First Day of the Month

[Kai77]

I have reports that need to be sent out daily comparing current MTD vs previous MTD. For example, if today is Mar 9, it'll be comparing Mar 1-9 data to Feb 1-9 data. But this breaks on the first day of each month. On Mar 1, Adobe is trying to compare Mar 1-1 vs Feb 1-1, but it returns 0 for the metrics on Mar 1 resulting in any % change or difference to be wrong. I'd like Adobe to use Feb vs Jan data on the day of Mar 1.

The solution logic can be a simple if statement, but it's hard to code that directly into Adobe. Does anyone have a workaround for this MTD vs PMTD issue on the first day of each month?

[Jennifer_Dungan · Accepted solution]

I don't think this is currently possible in Workspace... you can do something like that using Report Builder and Excel... you can create formulas to calculate the date range based on "today()" and some if statements to dynamically calculate the to and from date ranges for your "current" and "previous" time frames.

Then you use those cells to drive the start/end dates in Report Builder, pull in your data and then present the data all in Excel; you can even schedule this to send out automatically every day.

I did something similar to this with a monthly report that looked at YTD. When the report runs on Jan 1, instead of looking at this year... I look at the full last year and previous year. Then in Feb, it calculates the YTD and Previous YTD (ie. Jan this year vs Jan last year)