Get values for first month and last month

Hi Everyone!

I need to find average values from first and last month for some ID (for example)

This first and last month can be different for each ID and depends of filters, that can be applied.

It should be something like that:

IF Date = MinDate THEN AVG(Value), but i got error about nested calculations, because of MinDate measure.

Is there some way to get first/last date values?

Hello @iskorokhid, how are you building out the calculation to get your average value? In my testing for this, I created a minOver function to get the min date for each ID.
minMonth = minOver({Date Month}, [{id}], PRE_AGG)

Then we can create a calculation to get the average value for each id and each month:
avgByMonth = avgOver({value}, [{id}, {Date Month}], PRE_AGG)

Now you can utilize the minMonth field in your ifelse to check if the values match and return your avgByMonth field when it is true:
avgFirstMonth = ifelse({minMonth} = {Date Month}, {avgByMonth}, **here you can insert 0 or NULL**)

Then you can recreate that last calculation for last month as well! If there is an issue comparing minMonth with date, you may need to add some aggregation on date. You can use truncDate(‘MM’, {date}) if that is an issue.

I will mark this as a solution for now, but if you have any follow-up questions or issues, please let me know!

1 Like

Thanks, it’s works!

To be honest i found this solution already in some other topics, but didn’t understand why it works, after your explanation it’s more clear for me.

Big thanks one more time!

1 Like

Hello @iskorokhid, I’m happy to hear that helped, that is what we are here for!