split value monthly between two dates

[mauroanelli]

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?

[Zubair_Muhammad · Accepted solution]

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]
    )

[Zubair_Muhammad · Accepted solution]

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]
    )

[Zubair_Muhammad · Accepted solution]

HI @mauroanelli

See the attached file here

(With your sample data)

[mauroanelli]

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

[Zubair_Muhammad]

@mauroanelli

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

[mauroanelli]

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

[mauroanelli]

thank you very much!!! it works perfectly