Relative development of monthly amounts between actual and last year as percent value

[-Oliver-]

Hi,
I'm struggling with following problem:
In a bar chart with the dimensions
1. Company
2. [Month/Year]

the measure should be shown as a percent value of monthly amounts of the months of the actual year relative to the according month of last year.
The user only selects a month and a year and the system shows the bars from Jan to the selected month of the selected year for each company.

I've tried several versions to set the measure but without success. Last try is:

=(
(sum( {$ < Year = {$(#=only(Year))} , Day=, Month={">=1 <=$(=Max(Month))"}, Quarter=,[Month/Year]=>} Amount))
/
(sum( {$ < Year = {$(#=only(Year)-1)} , Day=, Month={">=1 <=$(=Max(Month))"}, Quarter=,[Month/Year]=>} Amount))
)
-1

In the chart the following error is shown:
"The chart is not displayed because it contains only undefined values"
If olny one sum()-line exists, bars will be displayed, but with wrong values of course.

Does somebody has any idea?
Thanks in advance

Oliver

[sidhiq91]

@-Oliver-  Could you please try something like below and let me know if it has worked.

Current Year: Sum({<OrderDate=P({<Year={"$(=Max(Year))"}>}OrderDate),
Month={$(=concat(distinct chr(39)& Month & chr(39),','))}
>}LineSalesAmount)

Previous Year Same Month: Sum({<OrderDate=P({<Year={"$(=Max(Year)-1)"}>}OrderDate),
Month={$(=concat(distinct chr(39)& Month & chr(39),','))}
>}LineSalesAmount)

You need to change the dimension as per your needs.

[edwin]

you will need to bridge your dates to associate current month with same month prior year.  this way when for example March 2022 is selected, March 2021 is also a possible selection

you of course add another field to indicate whetehr it is current year or prior year.

here is a sample code:

Data:
load Cust, addmonths(monthstart(today()),-iterno()) as Month, floor(rand()*100) as Amount
while iterno()<=36;
load 'C00'& iterno() as Cust while iterno()<4;
load 1 AutoGenerate(1);

tmpBridge:
load distinct Month
Resident Data;

inner join (tmpBridge)
load Month as Date Resident tmpBridge;

NoConcatenate Bridge:
load Month, Date, 'CURRENT' as DateType
Resident tmpBridge
where Month=Date;

Concatenate (Bridge)
load Month, Date, 'PRIOR' as DateType
Resident tmpBridge
where Month=addyears(Date,-1);

drop table tmpBridge;

to explain it field Dtae is from my calendar and the user selects Date, if i select specific dates:

and i select PRIOR, notice it is associated with prior year months, if i select CURRENT:
it is now associated with the current year months:

so now i can compare currenmt year month vs prior year month:

[-Oliver-]

Hi Sidhiq92,
thanks for the quick reply.
Unfortunately your proposal doesn't work. Each line only showing one bar of the actual month. 
To achieve a bar for each month, your formula must be changed to:
Sum({<OrderDate=P({<Year={"$(=Max(Year))"}, Month={">=1 <=$(=Max(Month))"}>}OrderDate)>}LineSalesAmount)

To clarify: each of my sum()-lines separately inserted in the measure showing the correct results.

But if I use both in the same measure function  and divide them mathematically to get a relative value the bars are not displayed anymore and the error is shown.

How must I build the formula for the relative measure? That is the question.