Forum Discussion

Kristofferaabo's avatar
Kristofferaabo
Icon for Helper IV rankHelper IV
8 years ago
Solved

Create a Calculated table

Hi, I'm new to calculated tables. But I think I need this as my raw dataset is not expressed the way I want it. Therefore I have two questions related to below raw data screenshot:         ...
General Comment
Need Help
Show and Tell
Tutorial Requests
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Kristofferaabo,

     

    Could you try the formula below to see if it works? :smileyhappy:

    Table =
SUMMARIZE (
    'Table1',
    'Table1'[project Code],
    'Table1'[last_name],
    "Budget approved", CALCULATE (
        FIRSTNONBLANK ( 'Table1'[actual_date], 1 ),
        FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget approved" )
    ),
    "Budget submitted", CALCULATE (
        FIRSTNONBLANK ( 'Table1'[actual_date], 1 ),
        FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget submitted" )
    )
)

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Kristofferaabo,

 

If I understand you correctly, you should be able to use the formula below to create a new calculate table to get the expected result in your scenario. :smileyhappy:

Table =
SUMMARIZE (
    'Table1',
    'Table1'[project Code],
    'Table1'[last_name],
    "Budget approved", CALCULATE (
        FIRSTNONBLANK ( 'Table1'[actual_date], 1 ),
        FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget approved" )
    )
)

Note: You'll need to replace 'Table1' with your real table name.

 

Regards

Kristofferaabo's avatar
Kristofferaabo
Icon for Helper IV rankHelper IV
8 years ago

Hi v-ljerr-msft, this is really cool. Exactly what I was looking for. Perhaps a stupid question, But I was trying to include another column as I wanted a couple of these columns in my data (basically because I want to calculate time between each columns).

I tried something lik ethis, but it gives me a "TRUE/FALSE error"

 

Table =
SUMMARIZE (
    'Table1',
    'Table1'[project Code],
    'Table1'[last_name],
    "Budget approved", CALCULATE (
        FIRSTNONBLANK ( 'Table1'[actual_date], 1 ),
        FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget approved" ),
"Budget submitted", CALCULATE (
        FIRSTNONBLANK ( 'Table1'[actual_date], 1 ),
        FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget submitted" )
) )

 

 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Kristofferaabo,

     

    Could you try the formula below to see if it works? :smileyhappy:

    Table =
SUMMARIZE (
    'Table1',
    'Table1'[project Code],
    'Table1'[last_name],
    "Budget approved", CALCULATE (
        FIRSTNONBLANK ( 'Table1'[actual_date], 1 ),
        FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget approved" )
    ),
    "Budget submitted", CALCULATE (
        FIRSTNONBLANK ( 'Table1'[actual_date], 1 ),
        FILTER ( 'Table1', 'Table1'[milestone_name] = "Budget submitted" )
    )
)

     

    Regards

    • Kristofferaabo's avatar
      Kristofferaabo
      Icon for Helper IV rankHelper IV
      8 years ago

      YOU ARE WIZARD! Thanks