Average per month group by

Hi, reporter9 

Hi,

Thank you for your quick response, I check your dax and find that the reason that causes this issue is that “format” funmction.

Because the “format” function return the “Text” type data , we can not compare the “Text” type data to the “Date” type data. And the other error is that “ _date < _quarter_end”. We can not use the _date as the condition, we need use the ‘Table’[month_date] because we are filtering the ‘Table’.

So in the end , you can try to user this dax:

Average2 = var _phase = SELECTEDVALUE('Table'[Phase])

var _date =VALUES('Table'[Month_Date])

var _quarter_end = DATE( YEAR( TODAY() ), QUARTER( TODAY() ) * 3 + 1, 1 ) - 1

var _duration =SUMX(FILTER( ALLSELECTED('Table'), 'Table'[month_date] in _date && 'Table'[Phase]=_phase) , [Duration])

var _count =COUNTROWS(DISTINCT(SELECTCOLUMNS( FILTER(ALLSELECTED('Table'),

'Table'[month_date] in _date

&& 'Table'[Month_Date]< _quarter_end

) ,"ID",[ID],"Month_date",[Month_Date])))

return

DIVIDE(_duration,_count)

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly