Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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]))
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.
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.
Many thanks, this solution definitely shows me possible approaches and helps me a lot in understanding.
"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.
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]))
Thank you very much, this is my preferred solution for my problem.
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |