Count/Sum Value only once a month

[ChrisCross]

Hi,

for the calculation of a key figure, only one value per employee may be considered within a month.

For a monthly view this can be done by a DISTINCT calculation of the employee. However, this does not work anymore if you drill down to a weekly or daily level.

The requirement is that even with a weekly view only the first value within a month is counted.

Date	Week	Month	Employee	Value
01.08.2021	2021/30	2021/08	A	1
02.08.2021	2021/31	2021/08	B	1
03.08.2021	2021/31	2021/08	C	1
01.08.2021	2021/30	2021/08	C	1
02.08.2021	2021/31	2021/08	A	1
03.08.2021	2021/31	2021/08	B	1

The right column shows the correct value in the month view (3). In the weekly view in week 31, employee A and C should no longer appear, because they were already counted in month 08, in week 30.

I am grateful for any help.

Many greetings

[lbendlin · Accepted solution]

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]))

[v-angzheng-msft · Accepted solution]

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.

[ChrisCross]

Many thanks, this solution definitely shows me possible approaches and helps me a lot in understanding.

[lbendlin]

@ChrisCross 

"for the calculation of a key figure, only one value per employee may be considered within a month."

What should happen when there are two entries for employee A on August 1st 2021?  Which one of these is "the first entry" ?

Here is a sample approach but the Totals are still wrong.

[lbendlin · Accepted solution]

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]))

[ChrisCross]

Thank you very much, this is my preferred solution for my problem.