topic Re: Show YTD Months in Column (Matrix) based selected Month in Slicer in DAX Commands and Tips

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

sanjaymithran — Mon, 24 Jun 2024 15:23:21 GMT

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

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

sanjaymithran — Mon, 24 Jun 2024 15:21:12 GMT

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

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

Anonymous — Tue, 25 Jun 2024 02:02:36 GMT

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

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

sanjaymithran — Tue, 25 Jun 2024 04:00:01 GMT

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

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

Anonymous — Tue, 25 Jun 2024 06:14:16 GMT

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".

Best Regards

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

sanjaymithran — Tue, 25 Jun 2024 08:48:00 GMT

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

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

Anonymous — Wed, 26 Jun 2024 01:39:05 GMT

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

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

sanjaymithran — Fri, 05 Jul 2024 13:39:23 GMT

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