Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Column Header Name in Calculation

  Hi,   Is it possible to lookup the column header’s name in a calculation. If I assign the column header a value based on its name, January 2019 could be assigned the value of 1, Feb-19 would be...
Need Help
MFelix's avatar
MFelix
Super User
7 years ago

Hi Anonymous,

 

What is the exact purpose of this can you share some sample and/or example of expected result?

 

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.

 

Regards,

MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      MFelix
      Super User
      7 years ago

      Hi Anonymous,

       

      Don't understand why you need to do this, can you please elaborate a little better (sorry for insisting).

       

      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:

       

       

      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" )
    )

       

      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.

       

      Regards,

      MFelix

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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<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/yzg2tjiea7x34gepvjk4dtj1j30kliaj
        PBI File: https://app.box.com/s/3yzr3vog8kmljhyftgd3qpgthpzqho3y