Column Header Name in Calculation

Question

&nbsp;Hi,&nbsp;Is it&nbsp;possible to lookup the column header’s name in a calculation.If I&nbsp;assign the column header a value based on its name,&nbsp;January 2019 could be assigned the value of 1, Feb-19 would be 2 and so forth. This way I can compare a month-year value system to replace values. To further elaborate on my end goal below see the if statement below.&nbsp;If(Headername = “Jan-19”, 1, 0) or use a switch statement.Then: if(2&gt;1, 150,0)With 2 being Feb-19.

mfelix · Answer

Hi&nbsp;Anonymous,
&nbsp;
Not really sure if this is what you need. But please check the PBIX file attached, Iremove all your calculated columns and created 2 measures highlited in green on the table.
&nbsp;
However I still think that the best solution would be to unpivot the columns since that would allow you to use time inteligence formulas.
&nbsp;
As I said check the two measures I created and if it's expected result if yes I will transform the rest of the data, since it's not yet looking at year and so on.
&nbsp;
Regards,
MFelix

mfelix · Answer

Hi&nbsp;Anonymous,
&nbsp;
What is the exact purpose of this can you share some sample and/or example of expected result?
&nbsp;
One way would be to do a unpivot of the columns and then make the IF statement on that column, but not really sure what you want to achieve.
&nbsp;
Regards,
MFelix

anonymous · Answer

If we unpivot the data it creates duplicates due to an employee status column. For every month of the year we want to assign a value to it from 1-12 based off the column name. January is 1, February is 2 and so on.

mfelix · Answer

Hi&nbsp;Anonymous,
&nbsp;
Don't understand why you need to do this, can you please elaborate a little better (sorry for insisting).
&nbsp;
Each column as a unique name so you can make calculation based on those names and no need to have new names. What I mean is that if you want to calculate the number of people with status Active in February vs January you need to create something like this:
&nbsp;
&nbsp;
Active Variation =
CALCULATE (
    COUNT ( Table[Jan-1] );
    FILTER ( ALLSELECTED ( Table[Jan-19] ); Table[Jan-19] = "Active" )
)
    - CALCULATE (
        COUNT ( Table[Jan-1] );
        FILTER ( ALLSELECTED ( Table[Feb-19] ); Table[Feb-19] = "Active" )
    )
&nbsp;
Since I don't know your data and expected result is difficult to give more help but just trying to understand why you need to change the name of the columns.
&nbsp;
Regards,
MFelix
&nbsp;

anonymous · Answer

MFelix,I need to calculate remaining hours each month by contractor over a 1 year period. Subtract hours worked from PO Hours during active contract dates. If a contractor works no hours subtract hours available to work that month. This gives us a burndown of hours in the contract. If the contract hasn't started yet or if the contract has ended, subtract 0.The problem we are having is that the hours available to work are subtracted before the contract begins and after the contract ends. I originally wanted to use the column headers to look up what month they were so I could assign a numerical value to each Month.Year and compare that to the start and end of each contractors Month.Year.To elaborate I would look up September 2018 and assign it a 9. If a specific contractor’s contract started in October (9&lt;10) it would not bill any hours for September. This is important because I want to see the total number of hours each contractor will use by month over the year using both a visual and a table.Using the same contractor as an example if they can work 160 hours in November but they have not submitted their hours yet I would want to use the 160 for the burndown amount unless the hours are submitted.Data and PBI file examples are below:Excel Data: https://app.box.com/s/yzg2tjiea7x34gepvjk4dtj1j30kliajPBI File: https://app.box.com/s/3yzr3vog8kmljhyftgd3qpgthpzqho3y

Forum Discussion

Column Header Name in Calculation

6 Replies

Related content

Dynamic Column headers

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

Dynamic Column Header in Visualisation

Line Break in Table Column Headers