Thanks for your reply, I think we are close to a solution. Monthyear is not formatted as a date as it is derived from other columns in the main dataset which link a month and year to an observation. It is created as year*100+month. When using your second solution the error shown is:
'A single value for column Monthyear in table TableName cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.'
Any way to work around this? Thanks in advance for your help.
I hope I am not going to overcomplicate it but yes there are.
Shown table would be for 1 object, which would report its TimeSinceNew multiple times during one month, so for each of these observations a MonthYear is created.
The complete database contains 1000s of objects, however I am limiting my table to 1 object for now to test out some reporting features (for which the time used during a month is the most crucial part).
Extended dataset for you to experiment on (here I turn off the 'Max of' filter for monthyear).:
I tried this, it says 'the function FILTER is not supported in this context in DirectQuery mode.' I'm sorry I never mentioned using this type of datasource before. Perhaps this only works when using a downloaded database?
Furthermore I managed to use the group function on the original dates to group (bins) the data per month, as I think this way it does read the column as dates. This however still does not work with any of the suggested formulas you gave before.