FILTER

[Syndicate_Admin]

Hello!

I have a table where I have a month number field and a year number field. I do segmentation with year number and month number. (There is no calendar table). I want to calculate the total sales for the segmented month, but also for the month before and after the selected month. What expression would the new measure have?

Thank you

[v-rzhou-msft · Accepted solution]

Hi @Syndicate_Admin ,

I suggest you to add a YearMonth column in your data table and then create measures to achieve your goal.

Previous Month Value = 
VAR _PREVIOUS =
    CALCULATE (
        MAX ( 'Table'[YearMonth] ),
        FILTER ( ALL ( 'Table' ), 'Table'[YearMonth] < MAX ( 'Table'[YearMonth] ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[YearMonth] = _PREVIOUS )
    )

Future Month Value = 
VAR _Future =
    CALCULATE (
        MIN ( 'Table'[YearMonth] ),
        FILTER ( ALL ( 'Table' ), 'Table'[YearMonth] > MAX ( 'Table'[YearMonth] ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[YearMonth] = _Future )
    )

Result is as below.

Best Regards,
Rico Zhou

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

[lbendlin]

Please add a calendar table to your data model.