Previous Month expression

[newbie_sm]

I have set the following  expression:

(
Sum({<Month={'$(=Max([Month], 'YYYY-MM'))'}>} Revenue)
/
Sum({<Month={'44927'}>} Revenue)
)
-1

where {<Month={'$(=Max([Month], 'YYYY-MM'))'}>} is Current Month

How can I set a similar expression for calculating Previous Month?  I tried this but it doesn't work:

{<Month={'$(=Max([Month]-1, 'YYYY-MM'))'}>}

[justISO]

Hi, try from max(Month) subtract 1:

{<Month={'$(=Max([Month], 'YYYY-MM')-1)'}>}

[newbie_sm]

This doesn't work.

The value returned is is '44958' and not '44927' which is the date value for 2023-01

[Aditya_Chitale]

@newbie_sm 

try using date() function to change format.

Regards,

Aditya

[newbie_sm]

Can you show me an example - I am new so not sure how the DATE function work

[Aditya_Chitale]

@newbie_sm 

try:  Date(max(Month),'YYYY-MM')

you might have to use num# too if the max(Month) value is evaluated in number format.

Date(num#(max(Month)),'YYYY-MM')

Regards,

Aditya

[MayilVahanan]

Hi

Based on ur sample, its looks like "Month"  has monthstart value.

You can try like below

Sum({<Month={'44927'}>} Revenue)

instead of 

Sum({<Month={"$(=Date(Addmonths(Max([Month], -1),'YYYY-MM')"}>} Revenue)

[newbie_sm]

This will not work as what this means is that I need to update the formula every month to the previous month's value

Is there a flexible formula to calaculate the previous month ?

Thanks

[MayilVahanan]

Hi

FYIP, its dynamic based on the max month value

Sum({<Month={"$(=Date(Addmonths(Max([Month], -1),'YYYY-MM')"}>} Revenue)

[newbie_sm]

Hei Mayil,

thanks for your guidance, I am getting closer to the correct formula.

Now, what happens is when I used this formula:

(Sum({<[Month Year]={'$(=Max([Month Year], 'YYYY-MM'))'}>} USD_Revenue) / Sum({<[Month Year]={'$(=Max(Date([Month Year]-1, 'YYYY-MM')))'}>} USD_Revenue)) -1

The result returned is:

(Sum({<[Month Year]={'Feb-2023'}>} USD_Revenue) / Sum({<[Month Year]={'2023-01'}>} USD_Revenue)) -1

as you can see from the result:

the 1st set of  {<[Month Year]={'$(=Max([Month Year], 'YYYY-MM'))'}>} returned result in month format = MMM-YYYY

while the 2nd set of {<[Month Year]={'$(=Max(Date([Month Year]-1, 'YYYY-MM')))'}>} returned result in month format = YYYY-MM

because of the different date format, the final calculated % is wrong.  

Can you guide me on how I can change the month format for the 2nd set to "MMM-YYYY'?

Note that in the Data Load Editor, the date & month formats have been set as:
SET DateFormat='MMM-YYYY';
SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

SET [Month Year]='MMM-YYYY';

Thanks for all your help!