Show YTD Months in Column (Matrix) based selected Month in Slicer

[sanjaymithran]

Hi ,

I need to create Matrix visual like below based on Month-Year slicer selection need to display YTD

Ex:1 Slicer  selection is Feb-24 then show as below

Ex:2 Slicer selection is  Dec-23 then Jan-23 to Dec-23 to show in column

I have tried some logic its working in table but not in Matrix

Thanks,

Sanjay

[v-yiruan-msft]

Hi @sanjaymithran,

You can follow the steps below to get it:

1. Create a separate date dimension table(DO NOT create any relationship with your fact table)

2. Create a measure as below to get YTD

YTD =
VAR _yearmonth =
    SELECTEDVALUE ( 'Date'[YearMonth] )
VAR _year =
    VALUE ( RIGHT ( _yearmonth, 4 ) )
VAR _month =
    SWITCH (
        LEFT ( _yearmonth, 3 ),
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6,
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            YEAR ( 'Table'[Date] ) = _year
                && MONTH ( 'Table'[Date] ) <= _month
        )
    )

Best Regards

[sanjaymithran]

Hi Yirvuan,

Thanks for your suggesstion, But Sales and Profit is separate measure (Calculated field)

Sales=Price*qty and Profit=selling price- cost price

Thanks,

Sanjay.

PREVIEW

[v-yiruan-msft]

Hi @sanjaymithran ,

I updated my sample pbix file(see the attachment), please check if that is what you want. You can toggle on the option "Switch values to rows".

Toggle on "Switch values to rows"

Best Regards

[sanjaymithran]

Hi Yiruvan,

This is working fine but column sub total is not displaying,

is there any way apply filter date column only because have 8 measures and each measure has already 100 lines

Thanks,

Sanjay

[v-yiruan-msft · Accepted solution]

Hi @sanjaymithran ,

Please create another 2 new measures as below and replace the original measure [Sales] and [Profit] on the matrix:

New_Sales = SUMX(VALUES('Table'[Date]),[Sales])

New_Profit = SUMX(VALUES('Table'[Date]),[Profit])

Best Regards

[sanjaymithran]

Hi yiruan,

when we used percentage field  in sub total just shows sum of percentage,its not show correctly

Show YTD Months in Column (Matrix) based selected Month in Slicer(Updated2) you shared this file example here created margin %=sales/profit as you mentioned formula individually coming correctly but total is wrong because final formula used sumx tried with averagex but not getting correct result

Thanks,

Sanjay

[sanjaymithran]

Hi ,

I need to create Matrix visual like below based on Month-Year slicer selection need to display YTD

Ex:1 Slicer  selection is Feb-24 then show as below

Ex:2 Slicer selection is  Dec-23 then Jan-23 to Dec-23 to show in column

I have tried some logic its working in table but not in Matrix

Thanks,

Sanjay