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...
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] )
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
i'm sorry it seems to works fine but it's not.
it works if you have date from gen to dec but for different dates all goes wrong
for example from 01/03/2018 to 30/06/2018 returns correct montly value but in may, june, july and august
Sad to hear this.
Please post some sample data and expected result...
Just like you did at the beginning of the post.
I will look into it
