fix the previous date (month)

[AhmDD]

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!

[Mark_Little]

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. 

[BrunPierre]

@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)

[EdgarOlmos]

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

[BrunPierre]

@AhmDD Is your question answered?

[vinieme12]

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