Forum Discussion
Recursive DAX Calculated Column
My company needs a scheduling report for the hours worked on machines. We can only use DirectQuery, not import due to security reasons. What I have so far is a table that has the HoursRemaining (t...
| Date | HoursPlanned | HoursRemaining | HoursRemainingCappedAtHoursPlanned | HoursUnderCapacity | HoursOverCapacity |
| 3/8/20 | 16 | 20 | 16 | 0 | 4 |
| 3/9/20 | 16 | 30 | 16 | 0 | 14 |
| 3/10/20 | 16 | 19 | 16 | 0 | 3 |
| 3/11/20 | 16 | 5 | 5 | 11 | 0 |
| 3/12/20 | 16 | 20 | 16 | 0 | 4 |
Are you looking for this one :
Cap = if([HoursRemaining]<[HoursPlanned],[HoursRemaining],[HoursPlanned])
This should be a column
Thank you, but what about the solution to adding up/subtracting the hours over capacity to the next days? Do you know how to solve this? amitchandak
This how I provided the last two measure on initial data
Diff = CALCULATE(sum(Sheet1[HoursPlanned])-sum(Sheet1[HoursRemaining]),FILTER('Date','Date'[Date]<=MAX('Date'[Date])))
//last two measure in you table
over cap cal = IF([Diff]<0,[Diff]*-1,0)
Over under cap = if([Diff]>0,[Diff],0)amitchandak I know, that's just giving me the same data that I started with. It's not recursively adding up the hours over capacity and subtracting the hours under capacity.