Forum Discussion
Add column to new table
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.
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.
3 Replies
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]))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.
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.
