Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter Formula with IF Statement

Hi all,

 

I'm trying to create a measure where I sum the total revenue field based on my Time Period filter (Month, Quarter, Year).  For the year, I need my measure to say that if it's not the current year then return the amounts for December only.  If it's the current year, then return last months amount (If it's August, show July).   I have a Month Number & Current Year column that hopefully helps.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    I have built a data sample from 2019/1/1 to 2021/12/31 (I think  Month Number & Current Year may better split to Year and Month column)

     

    And according to your description, you need to create a new table for slicer like this:

    ForSlicer = DISTINCT( SELECTCOLUMNS('Table',"Year",[Year],"Month",[Month]))

    Then create a measure and display it in Card visual:

    Measure =
VAR _year =
    SELECTEDVALUE ( ForSlicer[Year] )
VAR _month =
    SELECTEDVALUE ( ForSlicer[Month] )
RETURN
    IF (
        _year <> YEAR ( TODAY () ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Year] = _year && [Month] = 12 )
        ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', [Year] = _year && 'Table'[Month] = _month - 1 )
        )
    )

     

     

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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    I have built a data sample from 2019/1/1 to 2021/12/31 (I think  Month Number & Current Year may better split to Year and Month column)

     

    And according to your description, you need to create a new table for slicer like this:

    ForSlicer = DISTINCT( SELECTCOLUMNS('Table',"Year",[Year],"Month",[Month]))

    Then create a measure and display it in Card visual:

    Measure =
VAR _year =
    SELECTEDVALUE ( ForSlicer[Year] )
VAR _month =
    SELECTEDVALUE ( ForSlicer[Month] )
RETURN
    IF (
        _year <> YEAR ( TODAY () ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Year] = _year && [Month] = 12 )
        ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', [Year] = _year && 'Table'[Month] = _month - 1 )
        )
    )

     

     

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