Forum Discussion

tomperro's avatar
tomperro
Helper V
2 years ago
Solved

Filter Two Visuals-Visual 1 show only selected end month Visual 2 Show Previous 12 Months (Calendar)

I have a date slicer.
I need two matrix visuals.
Visual 1 needs to show only data for the month of the selected date.

Visual 2 needs to show the previous 11 months (calendar).
Example:
Date Slicer Selcted Value = 12/10/2023
Visual 1 should show counts for the entire month of December

Visual 2 should show counts by month for Jan, Feb, Mar, etc. up until Dec.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi tomperro 

    You can refer to the following solution.

    Sample data 

    The calendar table

    Relationship

    Create the following measure

    Sum = CALCULATE(SUM('Table'[Value]))
    Display =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        EOMONTH ( 'Table'[Date], 0 ) >= EOMONTH ( MAX ( 'Calendar'[Date] ), -11 )
            && EOMONTH ( 'Table'[Date], 0 ) <= EOMONTH ( MAX ( 'Calendar'[Date] ), 0 ),
        CROSSFILTER ( 'Calendar'[Date], 'Table'[Date], NONE )
    )
RETURN
    a

    Judge = IF([Display]<>BLANK(),1,0)

    The Sum measure is to display the value affected by the slicer to retun only selected month, the display measure top return pre 12month value.

    Create two matrix visual.

    and put the following field to the visuals.

    The first matrix

     

    The second matrix

    and put the judge visual to the second matrix visual filter.

     

    Output

    Best Regards!

    Yolo Zhu

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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi tomperro 

    You can refer to the following solution.

    Sample data 

    The calendar table

    Relationship

    Create the following measure

    Sum = CALCULATE(SUM('Table'[Value]))
    Display =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        EOMONTH ( 'Table'[Date], 0 ) >= EOMONTH ( MAX ( 'Calendar'[Date] ), -11 )
            && EOMONTH ( 'Table'[Date], 0 ) <= EOMONTH ( MAX ( 'Calendar'[Date] ), 0 ),
        CROSSFILTER ( 'Calendar'[Date], 'Table'[Date], NONE )
    )
RETURN
    a

    Judge = IF([Display]<>BLANK(),1,0)

    The Sum measure is to display the value affected by the slicer to retun only selected month, the display measure top return pre 12month value.

    Create two matrix visual.

    and put the following field to the visuals.

    The first matrix

     

    The second matrix

    and put the judge visual to the second matrix visual filter.

     

    Output

    Best Regards!

    Yolo Zhu

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