MTD Vs LMTD , Current Vs Previous Month, Daily & Monthly percentage folrmula

[UserID2626]

Hi,

My requirement is to get Total Amount  as percentage  in text box for MTD Vs LMTD, Current Month Vs Last Month, Daily , Monthly.

I have to show result  like below in text box

Total Amount for daily : 0.00%

Total Amount for monthly : 0.00%

Total Amount for MTD Vs LMTD : 0.00%

Total Amount for current Vs previous month: 0.00%

Kindly help me out to write the percentage expression for MTD Vs LMTD, Current Month Vs Last Month, Daily , Monthly.

Thanks !!!!

[Rohan · Accepted solution]

Hello ,

Try using Set Analysis equations like :

Yearly Change ie, Total Amount for YTD Vs LYTD  :

(Sum({<Date={">=$(=num(YearStart(max(Date)))) <=$(=num(MAX(Date)))"}>}Sales_Amt) => Current YTD ,

Sum({<Date={">=$(=num(AddYears(YearStart(max(Date)),-1))) <=$(=num(AddYears(MAX(Date),-1)))"}>}Sales_Amt)) = > Last Year YTD .

then you can use : 

[ (Current YTD-Last Year YTD)/Current Ytd ] *100 => this will give you Yearly Increase in %.

Monthly Change ie, Total Amount for MTD Vs LYMTD  :

(Sum({<Date={">=$(=num(YearStart(max(Date)))) <=$(=num(MAX(Date)))"}>}Sales_Amt) => Current MTD ,

Sum({<Date={">=$(=num(AddYears(MonthStart(max(Date)),-1))) <=$(=num(AddYears(MAX(Date),-1)))"}>}Sales_Amt)) = > Last Year MTD .

Then you can use :

[ (Current MTD - Last Year MTD)/ Current MTD ] * 100 = > MTD vs. Last Year MTD in %.

For Daily Change :

(Sum({<Date={"$(=num(MAX(Date)))"}>}Sales_Amt) => Today's Sales Total ,

(Sum({<Date={"=$(=num(MAX(Date)-1))"}>}Sales_Amt) => Yesterday's Sales Total.

Then You can use :

[ (Today's -Yesterday)/Today's  ]*100 => Daily Change in Sales.

You can extend these formulae to get Current vs Previous month. Current week vs Previous Week by slight changes in the range of date in the Set Analysis statements .

Let me know if you get the desired results or if any query arises.

Thanks & Regards.