SUMX Calculation

[Vatz8]

Hi, 

I am trying to add consecutive punch hours in consecutive hours column  if the difference between the current row and the previous row is less than 1.5. I have already added an index column and I am using the Sumx formula given below. I am however getting an error with the calculation as can be seen here. Can anyone please let me know what the issue might be and what can be done?

[v-chenwuz-msft]

Hi @Vatz8 ,

A simple workaround, change the earlier(vm_moc[employee]) to max(vm_moc[employee]).

Try this code:

Consecutive hours =
SUMX (
    FILTER (
        ALLSELECTED ((vw_moc),
        vw_moc[employee] = max ( vw_moc[employee] )
            && (
                LOOKUPVALUE ( vw_moc[TimeDifference], vw_moc[Index], vw_moc[Index] )
                    - LOOKUPVALUE ( vw_moc[TimeDifference], vw_moc[Index], vw_moc[Index] - 1 )
            ) < 1.5
            && vw_moc[Index]
                > LOOKUPVALUE ( vm_moc[TimeDifference], vw_moc[Index], vw_moc[Index] - 1 )
    ),
    vw_moc[punch_hours]
)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vatz8]

This formula is returning with an error, probably due to the allselected()

[daXtreme]

First, you should refrain from using

Use variables instead. Please go to EARLIER – DAX Guide and peruse the article. Then change your code accordingly.

Also, please refrain from using one-table models. Such a (bad) practice will create so many problems in no time that you'll be forced into doing it the right way anyway (and will be wasting time). Please read about how to create correct models that in fact do calculate what you want them to instead of giving you - RANDOMLY - wrong figures: Understand star schema and the importance for Power BI … (bing.com)

[Vatz8]

Hi, 

can you please provide a solution to this problem?