I have a staff category called Expat - I am trying to control number of expats so have defined a cap as an input to a measure. I then calculate whether, across multiple contracts, the total number of expats in a month exceeds the cap. Expats within Threshold are calculated as "HC (Expat - Capped) = MIN([HC (Expat)],[_UI: Expat Threshold])" and the excess is calculated as "HC (Expat - Excess) = [HC (Expat)]-[HC (Expat - Capped)]" - this gives me the right amounts when I show the information against a monthly timeline but does not allow me to capture the excess in a card (when the calculation is performed at a total level). How can I force the calculation to be performed at a date level? I have tried the following: HC (Expat - Excess) Total = SUMX(Sheet1,MAX(CALCULATE(SUM(Sheet1[Headcount]),FILTER(ALLSELECTED(Sheet1),Sheet1[Date].[Date]=EARLIER(Sheet1[Date].[Date]) && Sheet1[Nationality]="Expat"))-[_UI: Expat Threshold],0)) but it doesnt seem to be working either..
Any help would be much appreciated!
Gratefully,
Tim
