Filter Formula with IF Statement

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

[lbendlin]

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