Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sum two elements in the same column with a condition

Hello,

I want to do the following:

At my company there are two shifts in a day, and the data is uploaded when each shift ends.

So I have two rows per day.

I need to create two columns (as you can see in red) that sum the hours worked and not worked per day.

The function has to take into account the day, if it is the same, it must add the two values of hours

 

Can anybody help me?

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

    try like:

    Hs worked/day = 
VAR _worked =
SUMX(
    FILTER(
        TableName,
        TableName[Date]=EARLIER(TableName[Date])
    ),
    TableName[Worked]
)
RETURN
IF([Shift]="B", _worked)

Hs unworked/day = 
VAR _unworked=
SUMX(
    FILTER(
        TableName,
        TableName[Date]=EARLIER(TableName[Date])
    ),
    TableName[Unworked]
)
RETURN
IF([Shift]="B",_unworked)

    it worked like this:

     

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Anonymous 

     

    It is more advisable to do with table visuals, like this:

     

    2 reminders: 1) avoid the date hierarchy, 2) choose SUM for the two hour columns.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello, thank you for the reply.

      The problem with that is I need to make other functions after with the values of hours per day. So I need to do it in DAX.

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        hi Anonymous 

        try like:

        Hs worked/day = 
VAR _worked =
SUMX(
    FILTER(
        TableName,
        TableName[Date]=EARLIER(TableName[Date])
    ),
    TableName[Worked]
)
RETURN
IF([Shift]="B", _worked)

Hs unworked/day = 
VAR _unworked=
SUMX(
    FILTER(
        TableName,
        TableName[Date]=EARLIER(TableName[Date])
    ),
    TableName[Unworked]
)
RETURN
IF([Shift]="B",_unworked)

        it worked like this: