Aggregation by calculated column using 'FORMAT' function

[rylach]

Hi,

I created calculated column

settlemonth_mmmm_YYYY = FORMAT(issues[settlemonth];"mmmm YYYY")

(where issues[settlemonth] is a date field)

I put this field into 'rows' of table visual assuming that it will aggregate some numeric value (of the same 'issues') table by months, but, surprisingly, I got repeated rows for every unique issues[settlemonth] value.

Anyone can explain this? It behaves like that even after changing the calculated column definition to:

settlemonth_mmmm_YYYY = FORMAT(DATE(YEAR(issues[settlemonth]);MONTH(issues[settlemonth]);1);"mmmm YYYY")

TIA,

Ryszard.

[Greg_Deckler]

Would need to understand you data better, can you post sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[rylach · Accepted solution]

Thank you @Greg_Deckler  for pointing me to the FAQ entry, I'll use it... next time 😉

Preparing the sample data I found the solution. The calculated column was set to be sorted by the original column (issues[settlemonth], containing individual days). That was the reason for which it wasn't aggregated to months.

Cheers,

R.