Hi GD, I have a metric where I take the

LAST_VALUE

of a summed fact ordered by date (days). When I view the metric on monthly grain, the values shown represent the last day of the month which is expected. The problem comes when I add another dimension to this scenario, in this case it will find the last day this where each value of this dim was present and includes it in my results which is not what I want (I want whatever we have on the last day of the month). I tried using

IF_NULL

to put 0 so it will not take in other days but it did not work. Any suggestions on how to achieve this?

You’re running into a common “last snapshot vs. extra slicing” issue: when you add another attribute, the partition for LAST_VALUE changes and MAQL will return the last available day per each attribute value, not strictly the last day of the calendar month. Use one of these approaches to force “only the last day of the month” regardless of added dimensions: 1) Constrain by the month’s last date, then aggregate - First, compute the last date in the current context (and fix it for all rows):

SELECT MAX({label/report_date.day}) BY ALL OTHER

- Then use it in your metric to filter the fact to that date only; for example, sum at the last day of the month:

SELECT SUM({fact/size}) WHERE {label/report_date.day} = (SELECT MAX({label/report_date.day}) BY ALL OTHER)

When you view by month, the MAX day resolves to the month’s last day. Adding another dimension won’t shift the date, because BY ALL OTHER fixes the same last date for all rows in the insight. [[MAX](https://www.gooddata.com/docs/cloud/create-metrics/maql/aggregation/max/)] 2) Use LAST_VALUE correctly with an aggregation and avoid re-partitioning If you prefer LAST_VALUE syntax, aggregate first so there’s one value per day, then order by the date. Do not add the extra attribute into ORDER BY (that would re‑partition). For example:

SELECT LAST_VALUE(SUM({fact/size})) ORDER BY {label/report_date.day}

This returns the last day’s sum for the current date bucket (day/month/quarter/year). However, if the added attribute causes multiple values per day and you are not filtering to the exact last date, the function can still pick different “last available per slice.” Approach (1) is more robust when you want the exact last calendar day no matter the slicing. [[LAST_VALUE](https://www.gooddata.com/docs/cloud/create-metrics/maql/aggregation/last/)] 3) If you need last value per entity but sum across entities In cases where entities appear/disappear, one pattern is LAST_VALUE within the entity, then SUM:

SELECT SUM(   LAST_VALUE({fact/size}) ORDER BY {label/report_date.day}   WITHIN ({label/building_id}) )

Note this can include entities that didn’t exist on the very last day of the month (it sums their last value within the month), so it’s a different business behavior. Use only if you intend that. [[Slack thread](https://gooddataconnect.slack.com/archives/C04S1MSLEAW/p1716327881080049?thread_ts=1716327881.080049&cid=C04S1MSLEAW); [LAST_VALUE](https://www.gooddata.com/docs/cloud/create-metrics/maql/aggregation/last/)] Why IFNULL didn’t help IFNULL replaces nulls but does not change which rows/dates are evaluated; it won’t coerce LAST_VALUE to ignore earlier dates when additional dimensions are present. [[IFNULL](https://www.gooddata.com/docs/cloud/create-metrics/maql/conditionals/ifnull/)] Recommendation - Prefer approach (1): filter your metric to the max date in context BY ALL OTHER, then aggregate. This guarantees you always get “value on the last day of the month” even when you add more attributes to the insight. [[MAX](https://www.gooddata.com/docs/cloud/create-metrics/maql/aggregation/max/); [LAST_VALUE](https://www.gooddata.com/docs/cloud/create-metrics/maql/aggregation/last/)]

@kapa.ai I prefer the first approach but the MAQL you wrote doesn't work, how can I filter my base on the last day of the month/week?

A message from kapa.ai

Hello Amir, Thank you for the question. Our AI is still in development and I have noticed that you have given thumps down. May I ask the reason as it will improve the effectiveness of our AI tool? Thank you.

Hi, yeah it didn't really solve my problem and the 2nd answer was the repetition of the first. But it's still very cool, would be great if you guys could enable direct messaging to the app here on slack.

Hi Amir, do you still need assistance with the metric itself? It can be tricky based on how datasets are connected in the LDM. Are you also using the AI assistant in your workspace?

Hi Joseph, I would. The logic where date = max(date by all other) is hiding the date in the insight for some reason. Not sure if it's the correct solution. and we don't use AI in the workspace.

Hi Amir, could you please provide the exact maql expression you are using?