set analysis

[Anonymous]

HI,

I have the following variable:

vIncome=num(sum({<Month_Num={4},Year={2017}>} [TRUEPRICE]),'#,##0 ')

However, I do not want to change each month the "month_num" (which needs to the previous month).

What I need instead is

num(Month(today()))-1

How do I combine them?

Thank you,

Ronit

[oknotsen · Accepted solution]

I suggest you try this:

=num(sum({<Month_Num={$(=Month(today())-1)},Year={2017}>} [TRUEPRICE]),'#,##0 ')

[Anonymous]

Thank you!

[oknotsen]

Happy I could help .

[Anonymous]

So now I have:

=num(sum({<Month_Num={$(vMonthCal)},Year={2017}>} [TRUEPRICE]),'#,##0 ')

once vMonthCal==num(Month(today()))-1

and yet when i pick another quarter, the numbers change and not remain the numbers of the previous month.

How do I fix it..?

[vkhassanov]

You need to change Identifier to 1 like below:

=num(sum({1<Month_Num={$(vMonthCal)},Year={2017}>} [TRUEPRICE]),'#,##0 ')

By the way, if you need the formula to work further on, you will need to modify it:

1. remove the condition Year={2017}

2. as this formula will not work in January (because then num(Month(today()))=1 and 1-1=0) it's better to add continuous month count to your calendar like:

Month(Today())-Month([Date])+12*(Year(Today())-Year([Date])) as Month_Num_Cont

This expression will always give 0 for current month, 1 for previous month, 2 for month before previous and so on, so with this your formula will look like:

=num(sum({1<Month_Num_Cont={1}>} [TRUEPRICE]),'#,##0 ')