Hi @RichardLee
Your best bet is to create a field that has month and year in it, which you can do in the load script like this:
LOAD
[Invoice Date],
Date(MonthStart([Invoice Date]), 'MMM-YYYY') as [Invoice Month Year],
You can then simplify you statement for this MTD to be:
Sum(if([Value Type]='Actual' and [Invoice Month Year]=monthstart(today()) and Branch='North', AL_ANALVAL))
And the previous month is simply:
Sum(if([Value Type]='Actual' and [Invoice Month Year]=monthstart(addmonths(today(),-1)) and Branch='North', AL_ANALVAL))
You will find your statements are much more efficient if they use set analysis though, so the statements would be:
Sum({<[Value Type]*={'Actual'},[Invoice Month Year]*={'$(=date(monthstart(today()), 'MMM-YYYY'))'},Branch*={'North'}>}AL_ANALVAL)
and
Sum({<[Value Type]*={'Actual'},[Invoice Month Year]*={'$(=date(addmonths(monthstart(today()), -1), 'MMM-YYYY'))'},Branch*={'North'}>}AL_ANALVAL)
The other potential improvement is to change the monthstart(today()) to be max([Invoice Date]), and this will then show you the most recent selected date and the previous month. This way you can time travel and see the difference between other months.
For more ideas on prior period comparison you could take a look at:
https://www.quickintelligence.co.uk/prior-period-comparison/
Hope that helps,
Steve
