YTD and YTD PY for multiple measures

[Soc3]

I have created a number of measures for a variety of metrics and want to show them all in a matrix with YTD and YTD PY as two columns.

How can I convert the list of measures that I have into my desired format? I don't want to just switch values to rows because I want a more concise matrix.

Current matrix where all measures are values:

YTD 1	4334
YTD PY 1	3421
YTD 2	2564
YTD PY 2	1223
YTD 3	35234
YTD PY 3	3525

Desired matrix:

	YTD	YTD PY
1	4334	3421
2	2564	1223
3	35234	3525

Thank you!!

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i would like to try.  Share the download link of the PBI file with just 3 base measures and 3 YTD measures.

[Kedar_Pande]

@Soc3 

1. Create base measures (no YTD logic inside):

M1 = SUM('Table'[Metric1])
M2 = SUM('Table'[Metric2])
M3 = SUM('Table'[Metric3])
2. Create a measure field parameter
Modeling → New parameter → Fields → add M1, M2, M3.
Call it Metric Parameter.

3. Generic YTD + YTD PY using the parameter:

YTD Selected =
TOTALYTD ( SELECTEDMEASURE(), 'Date'[Date] )

YTD PY Selected =
CALCULATE (
[YTD Selected],
DATEADD ( 'Date'[Date], -1, YEAR )
)
4. Build the matrix:

Rows: Metric Parameter

Columns: YTD Selected, YTD PY Selected (as values)

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Soc3]

Hi @Kedar_Pande 

Thanks for your response. I am trying your method and getting stuck on step 3. What do you mean "using the parameter"? The YTD formulas don't refer to the parameter just created and I get an error using this code "There is no measure reference in the current context to be applied as SELECTEDMEASURE.

Thanks!

[v-priyankata · Accepted solution]

Hi @Soc3 

Thank you for reaching out to the Microsoft Fabric Forum Community.

I reproduced your scenario in Power BI Desktop using sample data and validated the behavior.

To achieve the desired matrix layout (metrics on rows with YTD and YTD PY as columns), the recommended approach is to:

• Create a disconnected Metrics table (e.g: 1, 2, 3).
• Keep your existing individual measures (Metric 1, Metric 2, Metric 3).
• Create dynamic YTD and YTD PY measures using SWITCH() that return the correct measure based on the selected metric.
• Use the Metrics table in Rows, and place only YTD and YTD PY in Values.

I’ve attached a sample .pbix file where this is implemented step by step for your reference. You can open it and adapt the pattern directly to your model.

Thanks.

[cengizhanarslan]

Create a table like this (manually or via DATATABLE):
MetricID → 1, 2, 3 (or meaningful names).
Put MetricID on matrix rows.

Then create two generic measures using SWITCH():

One measure for YTD that returns [YTD 1], [YTD 2], [YTD 3] based on selected MetricID.
Another measure for YTD PY that does the same for [YTD PY 1], [YTD PY 2], [YTD PY 3].

Place these two measures as the only values in the matrix.
This gives you a compact matrix with YTD / YTD PY as columns.

YTD =
SWITCH (
    SELECTEDVALUE ( Metrics[MetricID] ),
    1, [YTD 1],
    2, [YTD 2],
    3, [YTD 3]
)

YTD PY =
SWITCH (
    SELECTEDVALUE ( Metrics[MetricID] ),
    1, [YTD PY 1],
    2, [YTD PY 2],
    3, [YTD PY 3]
)

[danextian]

Hi @Soc3 

If that is how your raw data really is, transform your data in the query editor so it looks like below:

Please see the sample M code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WigxxUTBU0lEyMTY2UYrVgQgERILFjE2MDOFiRkABI1MzFEUgMUMjI2O4mDFIl6kRqlFQQVOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Value", Int64.Type}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Category], " ", {0, RelativePosition.FromEnd}), type text),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted Text After Delimiter", {{"Category", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Text After Delimiter", "Year"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}})
in
    #"Changed Type1"

If you actually have different measures  for different columns, the approach is to use calculation groups to apply the same previous year logic to different measures.

[Irwan]

hello @Soc3 

please check if this accomodate your need.

since you want to have another matrix visual with different format, then you need to create another table because the value are different from your original data and also matrix Rows and Columns does not accept measure.

1. create a new table with following DAX.

Table 2 =
GENERATE(
    SUMMARIZE(
        ADDCOLUMNS(
            'Table',
            "Text",
            SUBSTITUTE(
                'Table'[Column1],
                RIGHT('Table'[Column1],2),
                ""
            )
        ),
        [Text]
    ),
    SUMMARIZE(
        SELECTCOLUMNS(
            'Table',
            "Number",
            RIGHT('Table'[Column1],1)
        ),
        [Number]
    )
)

2. create a new measure that collect all your original measure.

Tranform Measure =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Column1]=SELECTEDVALUE('Table 2'[Text])&" "&SELECTEDVALUE('Table 2'[Number])
    ),
    [Measure]
)

3. plot into new matrix.

As mentioned by @lbendlin , this is "User Defined Functions in DAX", so you can have many other options.

Hope this will help where you can start.

Thank you.

[lbendlin]

The usual answer until recently would have been "Calculation Groups".  Nowadays the preferred answer is "User Defined Functions in DAX".  Have you considered that?

[Hans-Georg_Puls]

Hi @Soc3 , 

do I understand you right, that 1, 2 and 3 are placeholders for different metrics, but there will be more? Your goal is to have two columns for YTD and YTD PY and a row for every metric?