Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone!
I hove some simple case but can't solve it. There is some amount of sales for several years.
I can display fact sales by the months during all periods.
But I need to add amount of same month previous year to compare them on line graph (or any another visualisation).
It's ok if I use "Above" function. Like: Above (Measure_of_fact_sales, 12)
the result is something like this:
Where first column - period, second - amount of previouse year, third - amount of current year.
But when i use some period filter on dashboard (for example choosing just one year) - there is resulting only of current year:
You can see that measure for previouse year doesn't work when there is no any other periods in filter.
So I need to fix calculation of Measure to avoid any reaction to period filter. But it must be reaction to other filters - like regions, managers and etc.
Thank you a lot in advance!
HI @AhmDD
I tend to use the script to calculate any prior period by setting flags. Makes it simpler front end. Big problem you have is you appear to have the date a dimension and you trying to use it the calculation and not the selection.
@AhmDD Try these expressions;
Current month
SUM({$<Date = {"$(='>=' & addyears(monthstart (max(Date)),0)& '<=' & Date(Max(Date)))"},Year,Quarter,Month,Week>}Measure_of_fact_sales)
Same period the previous year
SUM({$<Date = {"$(='>=' & addyears(monthstart (max(Date)),-1)& '<=' & (AddYears(Max(Date), -1)))"},Year,Quarter,Month,Week>}Measure_of_fact_sales)
Hi.
You can make a master calendar to find year and month dimensions, then you can use expressions like this.
Last Year
sum({$<[YEAR] = {$(=max([YEAR]-1))},mes= {'$(=month(max([MONTH])))'}>} [MEASURE]),
Current year
=sum({$<[YEAR] = {$(=max([YEAR]))},mes= {'$(=month(max([MONTH])))'}, >} [MEASURE])
Regards
@AhmDD Is your question answered?
The problem here really is the Dimension being used
if you want to see Sales for all months then use Month as the Dimension and NOT YEARMONTH
202101 is associated to Year 2021 Month 01, the table will aggregate associated data only
Use Month dimension that does
Jan,Feb,Mar