Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure to subtract from a calculated column incorrect

Hi Community,

I have following measure yeilding incorrect result:  Hrs per month is a column & total leaves hours is a measure.

hrs worked = MAX('Emp Leave'[Hrs per month]) - 'Emp Leave'[Total Leave Hours]
 

My pbi file:  https://1drv.ms/u/s!Ag919_pO_UKrgSuhQlfClgQZWsbp?e=UIVJ3I

 

Many thanks in advance

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    The fundamental error in the operation turns out to be the lack of underlying data. This situation does not give you the results you expect.

    Maybe you can try this one.

    hrs worked = 
IF (
    SELECTEDVALUE ( 'Emp Leave'[Hrs per month] ) <> BLANK (),
    SELECTEDVALUE ( 'Emp Leave'[Hrs per month] ) - [Total Leaves Hours],
    BLANK ()
)

     

    Best Regards,

    Community Support Team _Charlotte

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

4 Replies

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

    Anonymous what is total leave hours measure, is it a simple sum or something else going in there?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      parry2k  Total leaves Hours measure is calculated based on leave start & leave end date, it also excludes  weeekends and  public holidays.

      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] >= EARLIER ( 'Emp Leave'[date_start] )
      && Datedim[Date] <= EARLIER ( 'Emp Leave'[date_end] )&& datedim[working days]=1
      )
      )
      ),
      [diff]*[Hrs per Day]
      ), CROSSFILTER('Emp Leave'[date_start],datedim[Date],None))
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Anonymous At a high level looking at this, I think the measure is the issue why you are not getting results, although I have to check and test it.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    The fundamental error in the operation turns out to be the lack of underlying data. This situation does not give you the results you expect.

    Maybe you can try this one.

    hrs worked = 
IF (
    SELECTEDVALUE ( 'Emp Leave'[Hrs per month] ) <> BLANK (),
    SELECTEDVALUE ( 'Emp Leave'[Hrs per month] ) - [Total Leaves Hours],
    BLANK ()
)

     

    Best Regards,

    Community Support Team _Charlotte

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