Forum Discussion

AlvinLy's avatar
AlvinLy
Helper II
2 years ago
Solved

Using Matrix column label in DAX calculation

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-012024-07-152024-07-292024-08-122024-08-262024-09-09
Period HoursPrevious incurredPrevious incurredPrevious incurredForecast CostForecast CostForecast 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. 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.