Anomaly in the ADDCOLUMNS function RRS feed

  • Question

  • Hi,

    Here's another one I cannot figure out.  I have a three column dataset with Date, Machine Number and Hours worked (File link - http://1drv.ms/1smaYvp).  I drag machine number to the Row labels and then compute the following calculated field formulas

    1. Machine Hours worked_sec2q4

    =SUM(sec2q4[Hours Worked])

    2. Last date of operation_sec2q4

    =MAXX(FILTER(sec2q4,sec2q4[Hours Worked]>0),sec2q4[Date])

    3. Last date of operation_sec2q4_try - An alternative method

    =CALCULATE(LASTDATE(sec2q4[Date]),sec2q4[Hours Worked]>0)

    4. Hours worked on last day_sec2q4

    =sumx(FILTER(ADDCOLUMNS(sec2q4,"LDO",MAXX(FILTER(sec2q4,sec2q4[Hours Worked]>0),sec2q4[Date])),[LDO]=sec2q4[Date]),sec2q4[Hours Worked])

    5. Hours worked on last day_sec2q4_try - An alternative method

    =SUMX(FILTER(ADDCOLUMNS(sec2q4,"LDO",[Last date of operation_sec2q4_try]),[LDO]=sec2q4[Date]),sec2q4[Hours Worked])

    All is well till point 4.  The answer in 5 is wrong.  The only difference between 4 and 5 is that in 4, I have typed the MAXX function where in 5, I have referred to the Last date of operation_sec2q4 measure.

    Why is the answer wrong?

    Also, when I type the following calculated field formula for computing "Hours worked on last day", the answer is wrong

    =SUMX(FILTER(ADDCOLUMNS(sec2q4,"LDO",CALCULATE(LASTDATE(sec2q4[Date]),sec2q4[Hours Worked]>0)),[LDO]=sec2q4[Date]),sec2q4[Hours Worked])

    What is the mistake with this calculated field formula

    Thank you.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, November 7, 2014 10:30 AM


All replies