Forum Discussion
Solved
split value monthly between two dates
hi all,
i have datas like this
| project | date start | date end | total value |
| a | 01/01/2018 | 31/12/2018 | 12 |
| b | 01/01/2018 | 31/12/2018 | 12 |
| c | 01/01/2018 | 31/12/2018 | 12 |
| d | 01/01/2018 | 31/12/2018 | 12 |
i need to split the value by numbers of month and have table like this
| project | month | monthly value |
| a | 01/01/2018 | 1 |
| a | 01/02/2018 | 1 |
| a | 01/03/2018 | 1 |
| a | 01/04/2018 | 1 |
| a | 01/05/2018 | 1 |
| a | 01/06/2018 | 1 |
| a | 01/07/2018 | 1 |
| a | 01/08/2018 | 1 |
| a | 01/09/2018 | 1 |
| a | 01/10/2018 | 1 |
| a | 01/11/2018 | 1 |
| a | 01/12/2018 | 1 |
| b | 01/01/2018 | 1 |
| b | 01/02/2018 | 1 |
| b | 01/03/2018 | 1 |
| b | 01/04/2018 | 1 |
| b | 01/05/2018 | 1 |
| b | 01/06/2018 | 1 |
| b | 01/07/2018 | 1 |
| b | 01/08/2018 | 1 |
| b | 01/09/2018 | 1 |
| b | 01/10/2018 | 1 |
| b | 01/11/2018 | 1 |
| b | 01/12/2018 | 1 |
i can do something with M but takes to much. any idea if its possible in dax?
Hi mauroanelli
You can try using this calculated Table in DAX
From the Modelling Tab >>>NEW TABLE
Table = VAR temp = ADDCOLUMNS ( GENERATE ( Table1, GENERATESERIES ( MONTH ( Table1[date start] ), MONTH ( Table1[date end] ) ) ), "No_of_Months", DATEDIFF ( Table1[date start], Table1[date end], MONTH ) + 1 ) RETURN SELECTCOLUMNS ( temp, "Project", [project], "Month", EOMONTH ( [date start], [Value] - 2 ) + 1, "MonthlyValue", [total value] / [No_of_Months] )
6 Replies
Hi mauroanelli
You can try using this calculated Table in DAX
From the Modelling Tab >>>NEW TABLE
Table = VAR temp = ADDCOLUMNS ( GENERATE ( Table1, GENERATESERIES ( MONTH ( Table1[date start] ), MONTH ( Table1[date end] ) ) ), "No_of_Months", DATEDIFF ( Table1[date start], Table1[date end], MONTH ) + 1 ) RETURN SELECTCOLUMNS ( temp, "Project", [project], "Month", EOMONTH ( [date start], [Value] - 2 ) + 1, "MonthlyValue", [total value] / [No_of_Months] )thank you very much!!! it works perfectly
