Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

FILTER

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.