Forum Discussion

AlvinLy's avatar
AlvinLy
Icon for Helper II rankHelper II
2 years ago
Solved

Issues with Calculate sum and getting infinity

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 ...
Need Help
  • AlvinLy's avatar
    AlvinLy
    2 years ago

    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's avatar
sevenhills
Icon for Super User rankSuper User
2 years ago

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