Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 measures are not resulting in correct values or results in no values. 'Emp Leave'[Hrs per month] and 'Emp Leave'[Hrs per Day] are calculated columns.
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous ,

     

    Try like

     

    Total Hours = (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]))))*8 *DISTINCTCOUNT('Emp Leave'[emp_code]) 


Total Leaves Hours = 
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] >= EARLIER ( 'Emp Leave'[date_start] )
                        && Datedim[Date] <= EARLIER ( 'Emp Leave'[date_end] )&& datedim[working days]=1
                )
            )
    ),
    [diff]*[Hrs per Day]
)

     

    File attached after signature

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak  thanks, I have managed to correct the 'Total Leave Hours'.

      As per your solution 'Total Hours' measure which is supposed to be 'Hrs per month' is not correct.

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        Anonymous , 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))