cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lyssillic
Helper I
Helper I

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 (the hours that need to be done that day), the HoursUnderCapacity (the hours that are leftover, are free), and the HoursOverCapacity (the hours that are over the planned hours for that machine on that day). The x-axis is the date.

hmm.PNG

 


We would like the HoursOverCapacity to be added on to the next day's over or under capacity. For example, if the previous day had hours over it's capacity, put it onto the remaining hours of the current day. If the current day had hours under capacity, the previous day's hours over capacity would subtract from the free hours that were available that day. And if there was hours over capacity of that day, then it would add the previous day's hours over capacity to the current day's.

 

I've created this 'TestingColumn,' which is a calculated column with the following DAX:

 

TestingColumn = 
    var prevHOC = 'Testing Totals'[prevHOC]
    var currentHOC = 'Testing Totals'[HoursOverCapacity]
    var currentHUC = 'Testing Totals'[HoursUnderCapacity]

    RETURN IF (
        prevHOC > 0,
        
        IF (
            currentHOC > 0,
            prevHOC + currentHOC,
            IF (
                currentHUC > 0,
                ABS(prevHOC - currentHUC),
                0
            )
        ),
        
        IF (
            currentHOC > 0,
            currentHOC,
            IF (
                currentHUC > 0,
                currentHUC,
                0
            )
        )
    )

 

 

adding.PNG


It correctly adds/subtracts the previous day's HoursOverCapacity to the current day's. However, when it goes to the day after to continue adding/subtracting HoursOverCapacity, it doesn't retrieve the updated value from the previous day that was just calculated. We need it to update the values as it goes, subtracting/adding and continuing the logic.

 

Here's an example of the data:       

Date HoursUnderCapacityHoursOverCapacity
3/8/20022.23
3/9/20016.58
3/10/20 11.64 0
3/11/20 018.08

                 

We want it to look like:

DateHoursUnderCapacityHoursOverCapacity
3/8/20022.23
3/9/20038.81
3/10/20027.17
3/11/20045.25


So adding 22.23 + 16.58 = 38.81,

Then subtracting 38.81 (over capacity hours) - 11.64 (free hours) = 27.17 (still over capacity),

And finally adding 27.17 + 18.08 = 45.25.

 

How can I do this within a DAX calculated column? Or a different, more efficient way?

Thanks!

14 REPLIES 14
Anonymous
Not applicable

The most efficient solution would be to use a measure instead of a calculated column.

 

I'd solve this by creating two measures:

Total Over Capacity = SUM(YourData[HoursOverCapacity]) - SUM(YourData[HoursUnderCapacity])
Over Capacity To Date = var filterDate = LASTDATE(YourData[Date])
var output = CALCULATE(
    [Total Over Capacity],
    ALL(YourData),
    YourData[Date] <= filterDate
)
RETURN
output

Capture.PNG

@Anonymous 

Okay, so that's close to what I'm looking for, but when the total hours over capacity is negative, or rather, there's hours under capacity, I don't want it to add those hours to the total, just the hours over capacity. I want the hours under capacity to be capped at a different variable, called HoursPlanned. 

Here's the new logic of something that I'd like:

 

TotalHOC = 
    IF (
        (SUM('Testing Totals'[HoursOverCapacity]) - SUM('Testing Totals'[HoursUnderCapacity])) > 0,
        SUM('Testing Totals'[HoursOverCapacity]) - SUM('Testing Totals'[HoursUnderCapacity]),
        IF (
            ABS(SUM('Testing Totals'[HoursOverCapacity]) - SUM('Testing Totals'[HoursUnderCapacity])) > SUM('Testing Totals'[HoursPlanned]),
            SUM('Testing Totals'[HoursPlanned]),
            ABS(SUM('Testing Totals'[HoursOverCapacity]) - SUM('Testing Totals'[HoursUnderCapacity]))
        )
    )

 

So, for an example with this picture; the hours under capacity is capped at 18.75, the constant of HoursPlanned, but I'd like the hours over capacity to be added/subtracted from the hours under capacity. I guess it would be better to have another measure?

hmm.PNG

Can you share sample data and sample output like the one before with planned hours too

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Yeah, here @amitchandak :

The HoursOverCapacity is when the HoursRemaining - HoursPlanned is positive.

The HoursUnderCapacity is when the HoursRemaining - HoursPlanned is negative (rather, when HoursPlanned - HoursRemaining is positive).

Thus, the HoursUnderCapacity can never be larger than the HoursPlanned, because you can't have more free hours of the day than what was planned for that day. (HoursUnderCapacity is capped at HoursPlanned)

DateHoursPlannedHoursRemainingHoursUnderCapacityHoursOverCapacity
3/8/20162004
3/9/201626010
3/10/20165110
3/11/20162140
3/12/20162004

 

Here's the output I'm looking for:

The 4 hours over capacity on 3/8/20 is added to the hours remaining of 3/9/20, equaling 30, which 30 - 16 = 14 for the HoursOverCapacity for that day. Then the 14 hours over capacity from 3/9/20 gets added to the hours remaining of 3/10/20, equaling 19. 19 - 16 = 3, which is the hours over capacity for 3/10/20. Then the 3 hours over capacity is added to hours remaining of 3/11/20, equaling 5. 5 - 16 = -11, which is negative, so 11 goes to 3/11/20's HoursUnderCapacity. The HoursUnderCapacity, however, doesn't go to the next day, only the HoursOverCapacity because it's carrying over the work that still needs to be done. The free hours of the day don't carry over to the next day, so 3/12/20 still has 4 hours over capacity. 

 

DateHoursPlannedHoursRemainingHoursUnderCapacityHoursOverCapacity
3/8/20162004
3/9/201630014
3/10/20161903
3/11/20165110
3/12/20162004

 

 

 

 

Please find a solution assuming you need to calculate it based on hour planned and hour remaining. Find attached file.

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak 

One last thing I forgot to mention; How do I cap the HoursRemaining to the HoursPlanned? (Just to show on the graph, so should I create a new column or measure?)

Please explain, what is needed

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak I've already figured out the logic you sent to me in the power bi file, calculating the hours over capacity and hours under capacity. Is there something I'm missing from your attached file or did you just redo what I did? I'm confused at this point, because for a second I thought you solved my problem, but that is not the case.

Can let me know the final column values you need on the file I shared back.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak 

DateHoursPlannedHoursRemainingHoursRemainingCappedAtHoursPlannedHoursUnderCapacityHoursOverCapacity
3/8/2016201604
3/9/20163016014
3/10/2016191603
3/11/201655110
3/12/2016201604

 

Are you looking for this one :

Cap = if([HoursRemaining]<[HoursPlanned],[HoursRemaining],[HoursPlanned])

 

This should be a column

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.