Forum Discussion

JasonP's avatar
JasonP
Frequent Visitor
8 years ago

Using a date table in a calculated table

I think this shouldn't be too complicated but I am still figuring out calculated tables and nothing I have tried works.    Basically, I have employee Ids in one table along with the store Id they w...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi JasonP,

Your formula just return 2016 result, you want to add other years' value in columns or rows? 

If you add as columns, please try the formula below, and check if it works fine.

Associate Sales =
ADDCOLUMNS (
    SUMMARIZE ( Agent, Agent[Agent], Agent[Associate], Agent[OfficeName] ),
    "GCI-2016", CALCULATE ( SUM ( 'Closed Trades'[Closed GCI] ), DateTable[Year] = 2016 ),
    "Split Fee-2016", CALCULATE ( SUM ( 'Closed Trades'[Split Fee] ), DateTable[Year] = 2016 ),
    "GCI-2017", CALCULATE ( SUM ( 'Closed Trades'[Closed GCI] ), DateTable[Year] = 2017 ),
    "Split Fee-2017", CALCULATE ( SUM ( 'Closed Trades'[Split Fee] ), DateTable[Year] = 2017 )
)


If you want to add it as rows, please try the formula below and check if it works fine.

Associate Sales =
ADDCOLUMNS (
    SUMMARIZE ( Agent, Agent[Agent], Agent[Associate], Agent[OfficeName] ),
    "Year", DateTable[Year],
    "GCI", CALCULATE (
        SUM ( 'Closed Trades'[Closed GCI] ),
        ALLEXCEPT ( DateTable, DateTable[Year] )
    ),
    "Split Fee", CALCULATE (
        SUM ( 'Closed Trades'[Split Fee] ),
        ALLEXCEPT ( DateTable, DateTable[Year] )
    )
)

If this is not want you want, please share your sample table and list expected result for further analysis.

Best Regards,
Angelia