Matrix with multiple categories and filters

[boldy]

Let me start by saying that I'm relatively new to Power BI and DAX.

I am looking to build a matrix that looks like the example below, with a goal of being able to utilize a slicer to select a week, which would then appear in the "Selected Week" column, with "MTD" and "YTD" being calculated up to the selected week.

I have a date table, containing calendar dates along with fiscal month/week of the year.
Example of the table the budget data lives in: 

Example of the sales table:

I have spent the last two weeks trying anything and everything I could think of or read about on forums. I eventually gave up due to time constraints and wrote conditional calculated columns for each variable. I feel as if this should be possible and that I'm just approaching the situation incorrectly.

Any assistance, hints, or fingers in the right direction are much appreciated.

Thank you for taking time out of your day to read this.


Edit:

Please disregard the above images.
This is an example of my desired output (though there will be more rows than Revenue, such as COGS, Net Margin, etc):

Here is an example of the data:

[xuexi1890]

@boldy 

first of all, try to give a consistent datasource for people to answer your question.

Try to use VAR in your Calculate() 

example: 

MTD Actual=

VAR _SelWeek=ALLSELECTED(Weeknum)

CALCULATE(

  SUM(Actual),
 FILTER(ALLSELECTED(TABLE1), Weeknum<=_SelWeek))

[xuexi1890]

@boldy 

Correction, forgot to add RETURN

MTD Actual=

VAR _SelWeek=ALLSELECTED(Weeknum)

RETURN

CALCULATE(

  SUM(Actual),
 FILTER(ALLSELECTED(TABLE1), Weeknum<=_SelWeek))

[boldy]

Hi @xuexi1890 - I truly appreciate the response and I apologize for not including data that would calculate. I have edited my post above.

Would you happen to know of a way to organize it so the matrix column has two levels? One for MTD, YTD, Prior Week and the other displaying Actual, Budget, Delta? I have been trying to use switch within a measure, but evaluating a column doesn't appear to work in a measure for me.

For example:

MTD Actual = 
VAR _SelWeek=ALLSELECTED(weeks[Weeks])
RETURN
CALCULATE(SUM(Actual[Revenue]),SWITCH('Time'[Time],"YTD",FILTER(ALLSELECTED(weeks),weeks[Weeks] <= _SelWeek)))

Or, is my only option to have 9 different columns named as MTD - Actual, MTD - Budget, MTD - Delta, etc?

Thank you again for the taking the time to respond yesterday.

Have a great evening!

[xuexi1890]

@boldy 

Hi Boldy,

it will be simpler if you could provide a PBIX file.

and for your question, what i understand is that you need to have 9 measures, not columns.

and my understanding of the SWITCH() doesn't allow you to present 3 measures at the same time. (either MTD, Selected week or YTD)

or if you are trying to add a column to aid your calculation, it would be more complex. (you need to flag every row of your data to be MTD or YTD), i don't suggest it

and i assume your data has dates, that would be easier to calculate MTD & YTD.

anyway, give me a PBIX file, and i'll try it out

[boldy]

@xuexi1890  - I truly appreciate the time you've given me on this subject so far. I'm happy to report that I discovered a way to get the exact format and functionality I was looking for! While there may be more graceful and better performing methods, this is what I got.

These are the relationships between the tables used, primarily for sorting purposes.

This is the 'type' table:

This is the 'time' table: 

This is the 'metrics' (unpictured, as it has no relationship to the other tables) table, which is referenced in the SELECTEDVALUE functions in the below measure. These values will eventually become row headers in the matrix: 

This is the 'merged' table, which actually displays the data: 

The 'merged' table contains one measure, with the below code:

overview_values = 
VAR fw = MAX(dates[fiscal_week_of_year])
VAR fm = MAX(dates[fiscal_month])
VAR fy = MAX(dates[fiscal_year])
VAR tmp_sales = SWITCH(
    SELECTEDVALUE(merged[Time]),
    "Prior Week",SWITCH(
        SELECTEDVALUE(merged[Type]),
            "Actual",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                        ... lists each item that appears on the row, referencing the measure created for each
                ),
                sales[fiscal_year] = fy,
                sales[fiscal_week_of_year] = fw
            ),
            "Plan",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                         ... lists each item that appears on the row, referencing the measure created for each
                ),
                dates[fiscal_year] = fy,
                dates[fiscal_week_of_year] = fw
            ),
             "Delta",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                         ... lists each item that appears on the row, referencing the measure created for each
                ),
                dates[fiscal_year] = fy,
                dates[fiscal_week_of_year] = fw
            )
    ),
    "MTD",SWITCH(
        SELECTEDVALUE(merged[Type]),
            "Actual",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                         ... lists each item that appears on the row, referencing the measure created for each
                ),
                dates[fiscal_year] = fy,
                dates[fiscal_month] = fm
            ),
            "Plan",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                         ... lists each item that appears on the row, referencing the measure created for each
                ),
                dates[fiscal_year] = fy,
                dates[fiscal_month] = fm
            ),
             "Delta",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                         ... lists each item that appears on the row, referencing the measure created for each
                ),
                dates[fiscal_year] = fy,
                dates[fiscal_month] = fm
            )
    ),
    "YTD",SWITCH(
        SELECTEDVALUE(merged[Type]),
            "Actual",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                         ... lists each item that appears on the row, referencing the measure created for each
                ),
                dates[fiscal_year] = fy
            ),
            "Plan",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                         ... lists each item that appears on the row, referencing the measure created for each
                ),
                dates[fiscal_year] = fy
            ),
             "Delta",CALCULATE(
                SWITCH(
                    SELECTEDVALUE(metrics[metric_index]),
                         ... lists each item that appears on the row, referencing the measure created for each
                ),
                dates[fiscal_year] = fy
            )
    )
)
RETURN
    tmp_sales

Basically, rather than using INSCOPE, I used SELECTEDVALUE to test which strings were at the intersection for each "cell" of the matrix. I then used SWITCH to provide my value at the end. Granted, there are probably far more efficient ways to go about this, but I've been using Power BI for all of 3 weeks or so. 

This results in a matrix looking like the one below, unfortunately my only example contains data that cannot be released, but I feel you will get the general idea. When used with a slicer or by selecting values in matrices/charts whose values are related to these, this matrix will change to reflect those values - depending on how the measures used to build this are set up.

Again, I am very appreciate of you taking the time out of your busy life to assist me. Your responses definitely led me down the path to build out this solution and I learned quite a bit along the way!

Regards