Calculated Measures as Rows and Columns in Matrix

[ryan_b_fiting]

Hello Community - 

I am looking for some help with presenting Multiple Measures within a matrix visual.  I have the following measures:

Sales = SUMX(Orders, Dollars_Sale)

COGS = SUMX(Orders, Order_cost)

$ Margin = Sales - COGS

I then have branched off of those measure to calculate my MTD, QTD, YTD measures.  What I am looking to do is create a matrix visual that will show my 3 measures (Sales, COGS, $ Magin) as rows, and then values be the MTD, QTD, YTD measures.

I know that you are able to switch the values to rows, but I do not want my To Date measures to show as rows, I want those to be the columns in the matrix.

Is there a way to create this type of Measure?  Below is what I would want the output to look like:

Measure	MTD	QTD	YTD
Sales	1000	1650	2900
COGS	600	1100	2275
$ Margin	400	550	625

Thank you in advance for any advice and input!

Ryan

[jdbuchanan71 · Accepted solution]

@ryan_b_fiting 

Take a look at the Calculation Groups functionality.  I belive it will accomlish what you are looking for.  Here are some videos discussing the topic and showing how to create them:
https://www.youtube.com/watch?v=vlnx7QUVYME&ab_channel=GuyinaCube

https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

[ryan_b_fiting]

@jdbuchanan71  this works great.  But I am having one issue with it.  When I want to look at MTD LY my calculation group does not calculate that properly.  I seem to always get 1 of 2 outputs.  It either gives me the Current MTD instead of LY.  Or it will give me LY but for the entire month,.

Here is the measure I am using in my Calculation group to try to get MTD LY for a partial month, but this gives me just Current MTD

VAR _MTD = TOTALMTD( SELECTEDMEASURE(), Rolling_Calendar[Date] ) RETURN  CALCULATE( _MTD, SAMEPERIODLASTYEAR(Rolling_Calendar[Date] ) )

Is there a way to calculate the parital MTD LY within calculation groups?  I want to be able to see my Sales for October 2019 but only through October 19th.

Thanks for all the insight! 

[jdbuchanan71]

There are some tricks you can use to stop future dates from coming into PY calculations.  Take a look at this article that goes through the steps of how to handle them.
https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/