Using Matrix column label in DAX calculation

[AlvinLy]

Hello,

I am looking for some help in utilizing a matrix's column label in a DAX calculation. I have a scenario where I want to track previously incurred cost and forecasting future cost. I want the matrix to look something like this

	2024-07-01	2024-07-15	2024-07-29	2024-08-12	2024-08-26	2024-09-09
Period Hours	Previous incurred	Previous incurred	Previous incurred	Forecast Cost	Forecast Cost	Forecast Cost
Cumulative Period

The row labels is just a simple table

Label

Period Hours

Cumulative Period

I have a date table with all the period dates as shown above 

I then have two fact tables, one is the incurred costs and another is forecast cost. I wanted to use a DAX formula where if the period date is less than or equal to the data update date, then ise the incurred cost table, and in all other scenarios use the forecast cost table. The problem i'm running to is how to utilize a switch statement while grabbing the column label (period date) as a value for the calculation at each cell. When I use SELECTEDVALUE(Date[Period]) for example, it displays blank. I'm only familiar with tables of  one dimension where SELECTEDVALUE will give me that row's instance of value. 

TLDR, is there a way to extract the column label in a SELECTEDVALUE or similar statement to have conditional matrix value formula, where condition is based on column label. 

[Anonymous · Accepted solution]

Hi, @AlvinLy 

Based on your description, I created the following sample data:

DateTable:

ForecastCostsTable:

IncurredCostsTable:

LabelTable:

I created a measure using the following DAX expression:

CostMeasure = 
VAR _current_row = SELECTEDVALUE(LabelTable[Label])
VAR _current_column = SELECTEDVALUE(DateTable[Period])
VAR _update_time = TODAY()
RETURN SWITCH(TRUE(),
    _current_row = "Period Hours",IF(
        _current_column<=_update_time,
        CALCULATE(SUM(IncurredCostsTable[Cost]),'IncurredCostsTable'[Period]=_current_column),
        CALCULATE(SUM(ForecastCostsTable[Cost]),'ForecastCostsTable'[Period]=_current_column)
    )
)

This will dynamically determine whether the current column is smaller than the data update date, and according to what you show, your data update date should be based on the date of your current region. If the update date is less, the cost is calculated using the IncurredCostsTable. Otherwise, use the ForecastCostsTable.

Here are the results:

I've uploaded the PBIX file I used this time below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.