Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Intelligent Headers for DAX

Hi @all,

 

For our report I have following challenge.

 

For every month I need a sum of forcast. Because there is no column with a date available I only have 

the column headers which indicates the month. [See below]

 

Is there a way that a DAX formular reads these headers and compair it with a time intelligence function in 

order to return the forcast value of the current month.

 

As far as I learned the table is not stable so that the column positions are changing, a VLOOKUP might not 

be possible.

 

ItemForcast SEPForcast OCT
A123321
B456654
C789321

 

Thank you so much for your help in advance.

 

Felix

Data Modeling
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    6 years ago

    Hi Anonymous, 

     

    You can go to Query Editor, select Forcast SEP and Forcast OCT columns then click on Unpivot Columns button to get below results: 

     

     

    Then split the Attribute column to get month name in a separate column. After you apply these changes, you can use DAX based on this month column with date table to achieve your requirement. 

     

     

    Best Regards,
    Qiuyun Yu 

1 Reply

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous, 

     

    You can go to Query Editor, select Forcast SEP and Forcast OCT columns then click on Unpivot Columns button to get below results: 

     

     

    Then split the Attribute column to get month name in a separate column. After you apply these changes, you can use DAX based on this month column with date table to achieve your requirement. 

     

     

    Best Regards,
    Qiuyun Yu