Matrix displaying quarterly, YTD, YTD PY, and YOY Var

[Soc3]

I've read the threads where the goal was similar but instead of quarterly it's monthly and I cannot successfully apply the required changes needed to instead show data by quarter plus YTD, YTD PY and YOY Var.

I tried using this logic:

Another Table =

VAR MonthAndNumber =

    ALL ( 'Table'[Month Name], 'Table'[Month Number] )

VAR AnotherTable =

    DATATABLE (

        "Number", INTEGER,

        "Measure", STRING,

        {

            { 13, "YTD Sales" },

            { 14, "Prior Year YTD" },

            { 15, "% Growth" }

        }

    )

RETURN

    UNION (

        SELECTCOLUMNS (

            MonthAndNumber,

            "Number", 'Table'[Month Number],

            "Measure", 'Table'[Month Name]

        ),

        AnotherTable

    )

And updated Month Number and Month Name to Quarter and Year but get an error that the a single value for a quarter cannot be determined.

Any help you can provide is greatly appreciated, I have spent so much time on this!

Goal:

 

[v-prasare]

Hi @Soc3,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Syndicate_Admin]

@Syndicate_Admin - Hi- no, I still have a pending question. See my clarification yesterday posted below. Thank you in advance!

[v-prasare]

Hi @Soc3,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Amankumar007]

You’re running into this issue because a Quarter is not a single-value column in the same way a Month Number is.

When you had Month Name + Month Number, each row was uniquely identifiable.
But once you switch to Quarter, multiple rows exist for the same quarter (Q1 has Jan–Mar, Q2 has Apr–Jun, etc.).

So when DAX evaluates this line

'Table'[Quarter]

inside a row context, it cannot determine a single value, which is why you see A single value for column ‘Quarter’ cannot be determined”

Why your current logic breaks

This part is the root cause:

MonthAndNumber = ALL ( 'Table'[Month Name], 'Table'[Month Number] )

When replaced with Quarter/Year:

• Quarter is not unique

• ALL() returns multiple rows per quarter

• SELECTCOLUMNS() expects one scalar value

• Result → ambiguity error

  Correct approach for Quarter + YTD scenarios

  Instead of deriving quarters from the fact table, build a disconnected axis table that explicitly defines what you want to show:

  • Q1–Q4

  • YTD

  • PY YTD

  • YoY %

    Step 1: Create a Quarter + KPI axis table

     

    Quarter KPI Axis = DATATABLE ( "SortOrder", INTEGER, "Label", STRING, { { 1, "Q1" }, { 2, "Q2" }, { 3, "Q3" }, { 4, "Q4" }, { 5, "YTD" }, { 6, "YTD PY" }, { 7, "YoY %" } } )

    No relationship to your date table

    • Used only for visual axis

    • Sort Label by SortOrder

      Step 2: Create a dynamic measure using SWITCH

       

      Sales – Quarter & YTD := VAR SelectedLabel = SELECTEDVALUE ( 'Quarter KPI Axis'[Label] ) RETURN SWITCH ( SelectedLabel, "Q1", CALCULATE ( [Total Sales], 'Date'[Quarter] = "Q1" ), "Q2", CALCULATE ( [Total Sales], 'Date'[Quarter] = "Q2" ), "Q3", CALCULATE ( [Total Sales], 'Date'[Quarter] = "Q3" ), "Q4", CALCULATE ( [Total Sales], 'Date'[Quarter] = "Q4" ), "YTD", TOTALYTD ( [Total Sales], 'Date'[Date] ), "YTD PY", CALCULATE ( TOTALYTD ( [Total Sales], 'Date'[Date] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ), "YoY %", DIVIDE ( [Total Sales] - CALCULATE ( TOTALYTD ( [Total Sales], 'Date'[Date] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ), CALCULATE ( TOTALYTD ( [Total Sales], 'Date'[Date] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) ) )

      Step 3: Use it in the visual

      • Axis → Quarter KPI Axis[Label]

      • Values → Sales – Quarter & YTD

      • Sort by SortOrder

        This will correctly show:

         

        Q1 | Q2 | Q3 | Q4 | YTD | YTD PY | YoY %

[Amankumar007]

You’re running into this issue because a Quarter is not a single-value column in the same way a Month Number is.

When you had Month Name + Month Number, each row was uniquely identifiable.
But once you switch to Quarter, multiple rows exist for the same quarter (Q1 has Jan–Mar, Q2 has Apr–Jun, etc.).

So when DAX evaluates this line

'Table'[Quarter]

inside a row context, it cannot determine a single value, which is why you see A single value for column ‘Quarter’ cannot be determined”

Why your current logic breaks

This part is the root cause:

MonthAndNumber = ALL ( 'Table'[Month Name], 'Table'[Month Number] )

When replaced with Quarter/Year:

• Quarter is not unique

• ALL() returns multiple rows per quarter

• SELECTCOLUMNS() expects one scalar value

• Result → ambiguity error

  Correct approach for Quarter + YTD scenarios

  Instead of deriving quarters from the fact table, build a disconnected axis table that explicitly defines what you want to show:

  • Q1–Q4

  • YTD

  • PY YTD

  • YoY %

    Step 1: Create a Quarter + KPI axis table

     

    Quarter KPI Axis = DATATABLE ( "SortOrder", INTEGER, "Label", STRING, { { 1, "Q1" }, { 2, "Q2" }, { 3, "Q3" }, { 4, "Q4" }, { 5, "YTD" }, { 6, "YTD PY" }, { 7, "YoY %" } } )

    No relationship to your date table

    • Used only for visual axis

    • Sort Label by SortOrder

      Step 2: Create a dynamic measure using SWITCH

       

      Sales – Quarter & YTD := VAR SelectedLabel = SELECTEDVALUE ( 'Quarter KPI Axis'[Label] ) RETURN SWITCH ( SelectedLabel, "Q1", CALCULATE ( [Total Sales], 'Date'[Quarter] = "Q1" ), "Q2", CALCULATE ( [Total Sales], 'Date'[Quarter] = "Q2" ), "Q3", CALCULATE ( [Total Sales], 'Date'[Quarter] = "Q3" ), "Q4", CALCULATE ( [Total Sales], 'Date'[Quarter] = "Q4" ), "YTD", TOTALYTD ( [Total Sales], 'Date'[Date] ), "YTD PY", CALCULATE ( TOTALYTD ( [Total Sales], 'Date'[Date] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ), "YoY %", DIVIDE ( [Total Sales] - CALCULATE ( TOTALYTD ( [Total Sales], 'Date'[Date] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ), CALCULATE ( TOTALYTD ( [Total Sales], 'Date'[Date] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) ) )

      Step 3: Use it in the visual

      • Axis → Quarter KPI Axis[Label]

      • Values → Sales – Quarter & YTD

      • Sort by SortOrder

        This will correctly show:

         

        Q1 | Q2 | Q3 | Q4 | YTD | YTD PY | YoY %

[maruthisp]

Hi @Soc3 ,

Can you try to check attached pbix file to achieve the solution which you are looking for? 

Please let me know if you have any further questions or need clarifications.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[MoizPatvi]

@Soc3 this pattern should address the scenario you’re trying to achieve.
Below is a clean, reusable approach that keeps the model flexible and the matrix fully controlled.

1) Create a disconnected Time-Frame table (Quarter + YTD + YoY)

Create a custom disconnected table that explicitly defines all column periods instead of relying on the date hierarchy.

This table includes:

• Dynamic quarters (Q1–Qn for the current year, Q1–Q4 for the prior year)

• YTD rows for both current and prior year

• Static YoY rows (YoY Var, YoY Var %)

Key design principles:

• One row per Metric × Time Period

• A numeric TimePeriodSort column to fully control column order

---

2) Build a single SWITCH-based measure

Create one master measure that dynamically responds to the selected time period.

The measure:

• Reads the selected Time Period

• Routes logic to:

  • Quarter values

  • YTD (Current Year / Prior Year)

  • YoY Variance

  • YoY Variance %

Pattern:

Selected Metric Value
= VAR _period = SELECTEDVALUE ( MetricsTimeFrameTableQuarter[Time Period] )
RETURN
SWITCH (
 TRUE(),
 _period starts with "Q", [Quarter Measure],
 _period starts with "YTD", [YTD Measure],
 _period = "YoY Var", [YoY Difference],
 _period = "YoY Var %", [YoY %]
)

---

3) Apply dynamic formatting

Use dynamic format strings (or conditional formatting) to ensure consistent formatting across periods:

• Currency format for Quarters and YTD

• Percentage format only for YoY Var %

Examples:

• Currency → $#,##0

• Percentage → 0.00%

---

4) Build the Matrix visual

Use a Matrix visual with:

• Rows: Product / Category (from the Fact table)

• Columns: MetricsTimeFrameTableQuarter[Time Period]

• Values: Selected Metric Value

---

5) Sorting (critical step)

In Model view:

• Select Time Period

• Sort by column → TimePeriodSort

This guarantees a stable and predictable column order across all visuals.

---

For the exact DAX, sorting logic, and final output, refer to the attached PBIX file:
https://drive.google.com/file/d/1Kcjh-vGgh66ulYJiI3Jb93TBaXAkKFlG/view

If you found this useful and enjoy advanced Power BI modeling patterns, feel free to connect with me on LinkedIn:
https://www.linkedin.com/in/da-moiz-patvi/

[MoizPatvi]

@Soc3  This pattern should address the scenario you’re trying to achieve.
Below is a clean, reusable approach that keeps the model flexible and the matrix fully controlled.

1) Create a disconnected Time-Frame table (Quarter + YTD + YoY)

Create a custom disconnected table that explicitly defines all column periods instead of relying on the date hierarchy.

This table includes:

• Dynamic quarters (Q1–Qn for the current year, Q1–Q4 for the prior year)

• YTD rows for both current and prior year

• Static YoY rows (YoY Var, YoY Var %)

Key design principles:

• One row per Metric × Time Period

• A numeric TimePeriodSort column to fully control column order

2) Build a single SWITCH-based measure

Create one master measure that dynamically responds to the selected time period.

The measure:

• Reads the selected Time Period

• Routes logic to:

  • Quarter values

  • YTD (Current Year / Prior Year)

  • YoY Variance

  • YoY Variance %

Pattern:

Selected Metric Value
= VAR _period = SELECTEDVALUE ( MetricsTimeFrameTableQuarter[Time Period] )
RETURN
SWITCH (
 TRUE(),
 _period starts with "Q", [Quarter Measure],
 _period starts with "YTD", [YTD Measure],
 _period = "YoY Var", [YoY Difference],
 _period = "YoY Var %", [YoY %]
)

3) Apply dynamic formatting

Use dynamic format strings (or conditional formatting) to ensure consistent formatting across periods:

• Currency format for Quarters and YTD

• Percentage format only for YoY Var %

Examples:

• Currency → $#,##0

• Percentage → 0.00%

4) Build the Matrix visual

Use a Matrix visual with:

• Rows: Product / Category (from the Fact table)

• Columns: MetricsTimeFrameTableQuarter[Time Period]

• Values: Selected Metric Value

5) Sorting (critical step)

In Model view:

• Select Time Period

• Sort by column → TimePeriodSort

This guarantees a stable and predictable column order across all visuals.

For the exact DAX, sorting logic, and final output, refer to the attached PBIX file:
https://drive.google.com/file/d/1Kcjh-vGgh66ulYJiI3Jb93TBaXAkKFlG/view

If you found this useful and enjoy advanced Power BI modeling patterns, feel free to connect with me on LinkedIn:
Moiz P

[Syndicate_Admin]

Hi @Syndicate_Admin - This is so close to what I am looking for, but the column I use as my "Sales" equivalent is a calculated column because I am converting currency. Even as a summed calculated column, I can't get any data to show in my matrix. That is the only thing I can see that is different than yours. Are you able to achieve the same when "Sales" is calculated?

[v-prasare]

Hi @Syndicate_Admin,

Could you please provide your sample pbix file so that we will try reproducing this scenario from our end, this helps us understand requirement and narrow down the issue.

thanks,

Prashanth

[danextian]

Hi @Soc3 

That kind of error happens when you're trying to create a measure which always expect a scalar value as a result (MAX, MIN, SUM, COUNT ) . The formula you gave is for a calculated table.