
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate previous month data based on selection
Hi all,
I am trying to work out how to display previous month's data based on the current month selection.
If I select May then I want Apr data displayed.
So far I have tried using the AddMonths function within set analysis (example below) but nothing is being returned.
=Sum({$ <date field={"$(=AddMonths(date field, -1)"}>} [Amount])
Any help will be much appreciated.
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be try by adding the missing closing parenthesis
=Sum({$<date field = {"$(=AddMonths(date field, -1))"}>} [Amount])
or you might need the Date() function with the format
=Sum({$<date field = {"$(=Date(AddMonths(date field, -1), 'DateFieldFormatHere'))"}>} [Amount])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I forgot to add that the date field is being converted to the 'fiscal' calendar, could that be causing the issue?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I don't think so, but it is easier to detect this if you are able to share a sample...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
When you are selecting the Month are you using a different field to "date field"? If so you should add that field to your set analysis to override the selection eg Sum({$ <date field={"$(=AddMonths(date field, -1)"}>} [Amount]) becomes
Sum({$ <date field={"$(=AddMonths(date field, -1)"}, Month=>} [Amount])
If you don't, the selection still applies and there is nothing on common with the date field set, resulting in nothing being returned.
