Count for max date in a month

[AlphaNik]

Hi everyone, 

I have a fact table with a unique id, a date and month name.

ex:

fact_id	fact_date	fact_monthName
1	21/01/2025	jan 25
2	31/01/2025	jan 25
3	07/02/2025	feb 25

I would like to write an expression to count my fact, but only for the last possible date of every month (based on current selection). in my example, if year 2025 would be selected, I should count id #2 for january and #3 for february.

I tried something like this:

=count( {<fact_date={"=aggr(max(fact_date), fact_monthName)"}>} distinct fact_id)

The expression is read, so it seems correct syntax wise, but the values are not what I expected. It seeems to count values for different dates.

Any help?

Thanks

[JandreKillianRIC · Accepted solution]

Hi @AlphaNik 

Try this 

count( {<fact_date={"=fact_date=aggr(max(fact_date), fact_monthName)"}>} distinct fact_id)

Let me know 

Regards Jandre

[AlphaNik]

Thank you, it worked.

Only thing, i had to create a temp table in order to order by date. For some reason it seems the value for aggr is evaluated only for the first row in load order (with monthName as a dimension). I tried to play with order by clause inside aggr to no avail. 

Anyway it's working!

Regards Nik

[JandreKillianRIC]

Hi @AlphaNik 

Great news! Im also struggling on an aggr sort. Should post something soon for other people to help me on this one! 

Regards Jandre