Issues with Calculate sum and getting infinity

[AlvinLy]

Hello,

I think this might be a simple DAX syntax issue that I am coming up with but i'm not quite sure. I'll start with the context.

I have a table that shows all the tasks in a schedule. It gives each tasks ID, start period, end period, no. of periods, hrs per period. This table is called 'Schedule' table

I initially tried creating a date table and a measure to get the number of hours per period. That didn't work so to troubleshoot i made a separate table called 'Test Hours' with all the period end dates and a calculated column instead. 

See a version of my code for the calculated column:

Schedule Hours = 
VAR Currentdate = 'Test Hours'[Period End Date]
VAR HrsP1 = CALCULATE(
    SUM('Schedule'[Hrs per Period]),
    'Schedule'[No of Periods] = 1,
    'Schedule'[End Period] = Currentdate
)
VAR HrsP2 = CALCULATE(
    SUM('Schedule'[Hrs per Period]),
    FILTER('Schedule', 'Schedule'[No of Periods] > 1),
    FILTER('Schedule', 'Schedule'[End Period] - (14*('Schedule'[No of Periods]-1)) <= Currentdate)
)
RETURN
HrsP1 + HrsP2

The math is pretty straightforward. If the number of periods for a task is 1 AND it lands on the date i'm evaluating, then I keep that row, if not it gets filtered out. If the number of periods is greater than 1 AND if any period date between start and end is the same or less than the current date then I keep that row, if not i gets filtered out. If period is 0 its invalid data or there are no hours associated.

Any help will do, i don't think i used any complex DAX functions but HrsP1 comes up to infinity and HrsP2 is a number but not the one i was expecting

[AlvinLy · Accepted solution]

Hello,

Thank you @sevenhills and @gmsamborn for your comments. 

I figured it out with some manipulation. First i was referencing the wrong table in my code, so that solved most of it. But the comment on using all for the filter helped when i changed my calculated column to a measure.

The revised code for a measure would be:

Schedule Hours = 
VAR Currentdate = 'Test Hours'[Period End Date]
VAR HrsP1 = CALCULATE(
    SUM('Schedule'[Hrs per Period]),
    'Schedule'[No of Periods] = 1,
    'Schedule'[End Period] = Currentdate
)
VAR HrsP2 = CALCULATE(
    SUM('Schedule'[Hrs per Period]),
    FILTER(ALL('Schedule'), 'Schedule'[No of Periods] > 1),
    FILTER(ALL('Schedule'), 'Schedule'[End Period] - (14*('Schedule'[No of Periods]-1)) <= Currentdate)
)
RETURN
HrsP1 + HrsP2

Thank you to both. 

I just want to add, if anyone else has similar issues with infinity, i think it's either incorrect reference or issues with relationships which causes a non stopping loop. That's what i found so far. 

[sevenhills]

glad to hear ALL helped. could you update the above reply with the correct DAX, so that it helps for anyone who checks this post in future!

[sevenhills]

Like @gmsamborn suggested, post small data and expected output it helps!

1) Are you doing running total or cumulative total? If yes, then you have to use ALL

2) Are you doing this calculated column in 'Test Hours' or 'Schedule'? then the formula will change!

3) Can you try 1st part working correctly?

Schedule Hours = 
VAR Currentdate = 'Test Hours'[Period End Date]
VAR HrsP1 = CALCULATE(
    SUM('Schedule'[Hrs per Period]),
    All( 'Schedule'),
    'Schedule'[No of Periods] = 1 && 'Schedule'[End Period] = Currentdate
)  

-- RETURN Currentdate -- Test it out first and make the sure the value is correct
RETURN HrsP1 -- Test it out second and make the sure the value is correct

[gmsamborn]

Hi  @AlvinLy 

I would like to help but I'm a little unclear about your requirements.

Can you provide the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

A .pbix file with sample data would be best.