Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sum IF with Filter

Hi, 
I have a transaction table (that is connected with Business Central) which has the following relevant  columns among others: Posting Date col, the name of the associates, Category,  and a column with number of hours worked.  I have been trying to write a measure through which I want sum the number of worked hours if date is after December 1, 2021 and the category columns are filtered based on certain conditions.  Here is what I came up with:

 
Total Worked Hours =
IF(
MAX(
'Ärendetransaktioner_Excel'[Posting_Date]
>=
DATE(2021,12,01)
),
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
FILTER( 'Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel'[Entry_Type] ="usage"
),
FILTER ('Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel' [Type] = "Resource"
),
FILTER( 'Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE()
),
"-"
)
)
 
It didn't quite work out  as I get an Error message "MAX function only accepts a column reference as argument".   I am not quite sure what the issue is even if I have spent hours researching the topic.  I would greatly appreciate if I could get som hints here. 
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thansk for your feedback AlexisOlson.

    I tried your code and even though it worked, it gave me a different result that I could not really verify.  However, when I realized that the filtering of the Poste_Date column in my transaction table is no different than the other filterings then I wrote the following and got the right result: 

     CALCULATE(
    SUM(
        'Ärendetransaktioner_Excel'[Quantity]
        ),
        'Ärendetransaktioner_Excel'[Entry_Type] ="usage", 
        'Ärendetransaktioner_Excel' [Type] = "Resource",
        'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE(),
        'Ärendetransaktioner_Excel'[Posting_Date] >= DATE(2021,12,01)
    )

     🙂 

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try like

     

    Total Worked Hours =
    IF(
    MAX('Ärendetransaktioner_Excel'[Posting_Date]) >= DATE(2021,12,01) ,
    CALCULATE(
    SUM(
    'Ärendetransaktioner_Excel'[Quantity]
    ),
    FILTER( 'Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel'[Entry_Type] ="usage" && 'Ärendetransaktioner_Excel' [Type] = "Resource" && 'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE() )
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your quick response.   && makes the code cleaner, but is not solving the problem with the first argument of my IF statement.   Still get error message!

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    The basic problem appears to be mismatching parentheses.

     

    Instead of this

    MAX ( Excel[Posting_Date] >= DATE ( 2021, 12, 01 ) )

     I think you want this:

    MAX ( Excel[Posting_Date] ) >= DATE ( 2021, 12, 01 )

    assuming [Posting_Date] is a table column and not a measure.

     

    Another issue is that IF needs to return the same data type for both branches, not a numeric quantity in some cases, and text "-" for others.

     

    Try this:

    Total Worked Hours =
IF (
    MAX ( 'Ärendetransaktioner_Excel'[Posting_Date] ) >= DATE ( 2021, 12, 01 ),
    CALCULATE (
        SUM ( 'Ärendetransaktioner_Excel'[Quantity] ),
        KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Entry_Type] = "usage" ),
        KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Type] = "Resource" ),
        KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Chargeable_PGS] = TRUE () )
    ),
    0
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thansk for your feedback AlexisOlson.

      I tried your code and even though it worked, it gave me a different result that I could not really verify.  However, when I realized that the filtering of the Poste_Date column in my transaction table is no different than the other filterings then I wrote the following and got the right result: 

       CALCULATE(
    SUM(
        'Ärendetransaktioner_Excel'[Quantity]
        ),
        'Ärendetransaktioner_Excel'[Entry_Type] ="usage", 
        'Ärendetransaktioner_Excel' [Type] = "Resource",
        'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE(),
        'Ärendetransaktioner_Excel'[Posting_Date] >= DATE(2021,12,01)
    )

       🙂