Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data below
DATE BUDGET ACTUAL
1/25/2021 192.65 110.86
2/25/2021 192.65 177.27
3/25/2021 71.88 113.78
4/25/2021 79.58 127.44
5/25/2021 77.01 127.36
6/25/2021 77.57
Now i would like to get the percentage of Actual data vs Budget Data for 1 to 5 only. which is 107%
My current formula is (SUM({<[MONTH <= MONTH(DATE)>} ACTUAL)/SUM({<[MONTH <= MONTH(DATE)]>} BUDGET)
but my result seems is not equal to 107%..
Appreciate your help.
Your set expression format is wrong, if you want to check if your month in the date is less than Month of today's date you should try something like this, but not sure if you got month field in your data or not.
(SUM({<[MONTH] = {"<$(=MONTH(Today()))"}>} ACTUAL)/SUM({<[MONTH] = {"<$(=MONTH(Today()))"}>}BUDGET)
Or if I just use your sample data, this could also work -
Sum({<ACTUAL = {"=Len(ACTUAL)>0"}>}ACTUAL)/Sum({<ACTUAL = {"=Len(ACTUAL)>0"}>}BUDGET)
Your set expression format is wrong, if you want to check if your month in the date is less than Month of today's date you should try something like this, but not sure if you got month field in your data or not.
(SUM({<[MONTH] = {"<$(=MONTH(Today()))"}>} ACTUAL)/SUM({<[MONTH] = {"<$(=MONTH(Today()))"}>}BUDGET)
Or if I just use your sample data, this could also work -
Sum({<ACTUAL = {"=Len(ACTUAL)>0"}>}ACTUAL)/Sum({<ACTUAL = {"=Len(ACTUAL)>0"}>}BUDGET)
Thanks... It works perfectly!