Having some trouble with this one, I want a fairly basic report, the last 12 months (dec 2019 through Nov 2020 at the moment) showing 12 bars on a combo chart for this year's sales data, and a line with the 12 months of last year's values for this period (dec 2018 through Nov 2019)
I have a measure for the last year like this:
Sum( { $< [TicketDate] = {">=$(=date(AddMonths(Today(),-24),'yyyy-MM-dd'))<$(=date(AddMonths(Today(),-12),'yyyy-MM-dd'))"}, [Unit]={'Ton'} > } [Qty] )
I'd like the report to show the Year & Month as the Dimension, but if I do this, I get 24 data points, the line for last year then the bars start this year, not a useful chart.
A second attempt is to use only the Month as the dimension, this looks more presentable, but the sorting order is Jan - Dec, even though it's showing last year's december at the end, which is confusing our users.
I've tried using sorting by expression but it never seems to do...absolutely anything at all. No matter what I put in "sort by expression" for the top item it never applies, sorting never changes.
Here's an example of a sort I tried, so last and current year should be counted as being the same month after adjustment:
=if( [TicketDate.autoCalendar.InYTD]
, [TicketDate.autoCalendar.YearMonth]
, AddYears([TicketDate.autoCalendar.YearMonth], 1)
)
Doesn't do anything
