topic Expression to display measure for previous month only in App Development

Expression to display measure for previous month only

RoyBatty — Mon, 14 Jul 2025 18:48:35 GMT

Hi,

We have the following table:

messages:
LOAD
    country,
    message_count,
    client_cost,
    sent_month
FROM [lib://QVDFOLDER/messages.qvd] (qvd);

sent_month is a Date in format MMM YYYY (e.g. Jun 2025) :

and Data model viewer shows the following tags:

On the sheet we added a table, and as the first column (dimension) we added country.

Now we want to add a new column (measure) that will display Sum([message_count]) for the previous month only.

We tried using the following expression:
Sum({<[sent_month] = {"=$(Date(MonthStart(AddMonths(Today(), -1)), 'MMM YYYY'))"}>} message_count)

But it doesn't work:

We checked the data and there are messages that were sent in the previous month, but when we use the above expression - it shows an empty table.

We tested this part of the expression by adding it to be a label - and it works:

Date(MonthStart(AddMonths(Today(), -1)), 'MMM YYYY')

Do you know why the above expression doesn't work, what might need to be changed to make it work?

Re: Expression to display measure for previous month only

Digvijay_Singh — Mon, 14 Jul 2025 21:25:43 GMT

Try this may be -

Sum({<[sent_month] = {"=$(=Date(MonthStart(AddMonths(Today(), -1)), 'MMM YYYY'))"}>} message_count)

Check in the expression editor at the bottom, it should show right value of the expression used in sent_month.

Somehow formatting should match between data model value and the value resulting from the above expression. Check for decimal component in the date as well, sometimes that causes the mismatch.

Re: Expression to display measure for previous month only

RoyBatty — Tue, 15 Jul 2025 12:55:32 GMT

I tried that and now the editor shows June 2025 (please ignore the "msg." prefix - I've excluded it here for simplicity):

but for some reason it still doesn't work:

Re: Expression to display measure for previous month only

RoyBatty — Tue, 15 Jul 2025 13:03:57 GMT

This works (the problem was with the quotes):

Sum({<[sent_month] = {"$(=Date(MonthStart(AddMonths(Today(), -1)), 'MMM YYYY'))"}>} message_count)

(and also with missing "="... Thanks @Digvijay_Singh !)