Count/Sum Value only once a month

Ok here is a better version that calculates the total correctly.

 

Adjusted Value = 
var g = GROUPBY(Employees,Employees[Employee],"md",minx(CURRENTGROUP(),Employees[enDate]))
var h = ADDCOLUMNS(g,"First",CALCULATE(min(Employees[enDate]),ALLEXCEPT(Employees,Employees[Employee],Employees[Month])))
return countrows(filter(h,[First]=[md]))

 

 

Hi, ChrisCross 

Try to create measures as follows:

_week = 
var _week=RIGHT(MAX('Table'[Week]),2)
return VALUE(_week)

_Year = 
var _year=LEFT(MAX('Table'[Week]),4)
return VALUE(_year)

_isNew =
VAR _year = [_Year]
VAR _lastweek = [_week] - 1
VAR _t =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Week]
            = CONCATENATE ( [_Year], "/" & _lastweek )
    )
VAR _emp_t =
    SUMMARIZE ( _t, [Employee] )
VAR _if =
    IF ( MAX ( 'Table'[Employee] ) IN _emp_t, 0, 1 )
RETURN
    _if

Then show items when the value is 1.

Now create the measure of sum and count:

_emp = DISTINCTCOUNT('Table'[Employee])

_sum = 
var _sum=SUM('Table'[Value])
var _sumWeek=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Week]=MAX('Table'[Week])))
var _sumMonth=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])))
var _sumAll=CALCULATE(SUM('Table'[Value]),ALL('Table'))
var _if=IF(ISINSCOPE('Table'[Employee]),_sum,IF(ISINSCOPE('Table'[Week]),_sumWeek,IF(ISINSCOPE('Table'[Month]),_sumMonth,_sumAll)))

return _if

So the result like this:

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

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