Forum Discussion
Measure involving caluclated column results wrong values
Hi Community, I have 2 measures: hrs worked = MAX('Emp Leave'[Hrs per month]) - 'Emp Leave'[Leaves in Hrs] Leaves in Hrs = 'Emp Leave'[leaves]* max('Emp Leave'[Hrs per Day]) Both me...
amitchandak
Super User
Super UserAnonymous , Try these two
Total Hours = Var _1 = (COUNTROWS(filter(ADDCOLUMNS( CALENDAR(min(datedim[Date]), Max(datedim[Date])),"Workday", if(WEEKDAY([Date],2)<6,1,0)),[Workday]=1)) - COUNTROWS(filter('Public holiday','Public holiday'[date] <=max('datedim'[Date]) && 'Public holiday'[date] >=Min('datedim'[Date]))))
var _2 =CALCULATE(Max('Emp Leave'[Hrs per Day]), filter(All('Emp Leave'),'Emp Leave'[emp_code] =max('Emp Leave'[emp_code]) ))
return
Sumx( Addcolumns( SUMMARIZE('Emp Leave','Emp Leave'[emp_code],datedim[Date].[MonthNo]),"_3" ,_2 *_1),[_3])
Total Leaves Hours =
CALCULATE( SUMX (
ADDCOLUMNS (
SUMMARIZE ('Emp Leave', 'Emp Leave'[emp_code], 'Emp Leave'[date_start], 'Emp Leave'[date_end],'Emp Leave'[Hrs per Day] ),
"diff",
COUNTROWS (
FILTER (
Datedim,
Datedim[Date] >= ( 'Emp Leave'[date_start] )
&& Datedim[Date] <= ( 'Emp Leave'[date_end] ) && datedim[working days]=1
)
)
),
[diff]* [Hrs per Day]
), CROSSFILTER('Emp Leave'[date_start],datedim[Date],None))