Using a date table in a calculated table

[JasonP]

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 work from. I also have formulas written to calculate their sales which is taken from another table that has a prebuilt relationship between the employee Id and sale. 

Where I get stumped is trying to break down the sales by employee by year using the date table I have built. Essentially what I am looking for is a table that each empolyee is listed along with their store per year with their sales from that year.

I built the table below which works perfectly except I now don't want just 2016, I want to see each year adding in a year column to the table. Any tips?

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

[v-huizhn-msft]

Hi @JasonP,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia

[v-huizhn-msft]

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

[TomMartens]

Hey,

it would be really helpful if you could make up a sample pbix file and provide the link to this file either on onedrive or dropbox.

Regards

Tom