locked
DAX Question regarding context of LastDate when used in Calculate RRS feed

  • Question

  • 

    I am trying to write a DAX formula where it sums a measure where it use only the records where the column Site = "OSK", the column Group <> Manufactur and where the date of records used is the LastDate.

    Here is the formula.

    =calculate(sum('StandardCost'[CostPerUnit]),StandardCost[Site]="OSK",StandardCost[Group]<>"Manufactur",LastDate('StandardCost'[Date]))

    The LastDate statement does not find the LastDate as limited by Site and Group. It finds the LastDate of the group ignoring those filters.

    Can someone tell me what I am doing wrong?

    Thanks!

    Mike Ward


    Monday, January 28, 2019 8:05 PM

Answers

  • This is the expected behaviour. The 3 filter conditions to your CALCULATE() are evaluated independently. If you want the LastDate calculated in the context of the other filters you'd need to specify that. If you are using Excel 2016 you could do this with variables.

    eg.

    =
    var lastCostDate = CALCULATE(LastDate('StandardCost'[Date]),StandardCost[Site]="OSK",StandardCost[Group]<>"Manufactur")
    var lastCost = calculate(sum('StandardCost'[CostPerUnit]),StandardCost[Site]="OSK",StandardCost[Group]<>"Manufactur",'StandardCost'[Date] = lastCostDate)
    return lastCost


    http://darren.gosbell.com - please mark correct answers

    Wednesday, January 30, 2019 2:38 AM