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

[tomperro]

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.

[Anonymous · Accepted solution]

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.