Forum Discussion
split value monthly between two dates
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
thanks, look at this. the first row is like the old one, start and ends in the same years, second row i change the start/end and set it across two years
this is what i got
i think that the problem is within generateseries, because the month start end wont work
