Forum Discussion

mauroanelli's avatar
mauroanelli
Helper I
8 years ago
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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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]
    )
mauroanelli's avatar
mauroanelli
Helper I
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      mauroanelli
      Helper I
      8 years ago

      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