Add column to new table

[ZunzunUOC]

Hi again, I have a table (Report Diario) with the next columns:

AGREEMENT DATE

END DATE

AMOUNT

REGION

In a new table (CalendarioAdelantado) I have the next columns:

DATE

AMOUNT

On the last table, column amount is a calculated column with the next code:

Amount = CALCULATE(SUM('Report Diario'[Signed Monthly Billing In Local Currency]);FILTER('Report Diario';AND(CalendarioAdelantado[Date]<'Report Diario'[Agreement Date];AND(CalendarioAdelantado[Date]>'Report Diario'[End Date];NOT(ISBLANK('Report Diario'[End Date]))))))

That said, I need to add a new column on the CalendarioAdelantado table with the column "Region". Thus, If I have

AGREEMENT DATE	END DATE	AMOUNT	REGION
01/07/2019	15/06/2019	100	SOUTH
01/07/2019	15/06/2019	100	NORTH
01/07/2019	15/06/2019	100	EAST
01/07/2019	15/06/2019	100	SOUTH

I need the next result:

DATE	AMOUNT	REGION
15/06/2019	200	SOUTH
15/06/2019	100	EAST
15/06/2019	100	NORTH

Any idea? Thanks.

[v-frfei-msft]

Hi  @ZunzunUOC ,

Please refer to the below expression to generate the calculate table:

Table = SUMMARIZE('Report Diario',[AGREEMENT DATE],'Report Diario'[END DATE],[REGION],"Amount",SUM('Report Diario'[AMOUNT]))

[ZunzunUOC]

Hi @v-frfei-msft , thanks for your effort.

I going to try to explaing better:

I have the next table:

And I need to show the next:

In every day I have between, agreement and end-Date, I need to show a data per region.

[ZunzunUOC · Accepted solution]

Finally, here the solution:

Table = SELECTCOLUMNS(FILTER(CROSSJOIN(CALENDAR("01/01/2018";"01/01/2020");'Report Diario');[Date]>='Report Diario'[Fecha de Cierre]&&[Date]<='Report Diario'[Fecha Comprometida] && NOT(ISBLANK('Report Diario'[Fecha de Cierre])));"FECHA";[Date];"REGION";'Report Diario'[Sales Region];"ADELANTADO";'Report Diario'[Signed Monthly Billing In Local Currency])

I found it in another forum. Thanks.