Forum Discussion

BachFel's avatar
BachFel
Helper II
8 years ago
Solved

SUMIFS Problem

Ho together,   I´m new at PBI and look for an aquivalent for the Sum IF Function in Excel:   I have to tables. 1. one is calles "KontElement" and contains the columns [CSAuftrag] and [Budget]. T...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi BachFel

     

    You can use this calculated column in Table 1 i.e. KontElement

    Assuming 2 tables are related on column CSAuftrag

     

    =
CALCULATE ( SUM ( Table2[Leistung] ) )

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi BachFel

     

    Looks good to me. May be I am missing something

     

    Could you show what you are getting and what you expect?

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi BachFel

     

    Try this

     

    WorkingTime_Jan =
CALCULATE (
    SUM ( LN[ArbeitszeitInH] ),
    FILTER (
        VALUES ( LN[Dates] ),
        MONTH ( LN[Dates] ) = 1
            && YEAR ( LN[Dates] ) = 2018
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi BachFel

 

Looks good to me. May be I am missing something

 

Could you show what you are getting and what you expect?

BachFel's avatar
BachFel
Helper II
8 years ago

Hi Zubair_Muhammad

 

there is another task:

 

 

WorkingTime_Jan = Calculate ( Sum (LN[ArbeitszeitInH]))

There is a column with dates in the table "LN". How can I improve the formula above to get only the operations of january 2018?

 

The format of the dates is: dd.mm.yyyy

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi BachFel

     

    Try this

     

    WorkingTime_Jan =
CALCULATE (
    SUM ( LN[ArbeitszeitInH] ),
    FILTER (
        VALUES ( LN[Dates] ),
        MONTH ( LN[Dates] ) = 1
            && YEAR ( LN[Dates] ) = 2018
    )
)