Distributing value equally between months

[hedin123]

Hello all! 

I am currently doing a dashboard for budgetting. I want to see the budget each month. The problem is that the budget comes in the format as  01/01/year. So now it's only "January" each time. So now I want to distribute this data 1/12*value month by month. Is there any easy way to transform the data to do this?  See picture below. 

Thanks in advance! 😄 

[v-yangliu-msft]

Hi  @hedin123  ,

Is the result up to your expectation

Here are the steps you can follow：

1. Create calculated column.

12/1 =
var _1=CALCULATE(SUM('Table (3)'[value]),FILTER('Table (3)',YEAR('Table (3)'[dato])=YEAR(EARLIER('Table (3)'[dato]))&&MONTH('Table (3)'[dato])=MONTH(EARLIER('Table (3)'[dato]))))
return
DIVIDE(_1,12)

Column 2 = YEAR('Table (3)'[dato])&"-"&MONTH('Table (3)'[dato])&"-"&'Table (3)'[12/1]

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hedin123]

Hello @v-yangliu-msft  and thanks for your answer! 😄 

maybe my formulation was wrong. What I want to is for each value, for instance Wednesday 1. January  has a value of 427 when dividing by 12. I want to put this value on january, february, march... to december. so that i can plot months.

[v-yangliu-msft]

Hi  @hedin123  ,

Create data:

Here are the steps you can follow：

1. Create calculated column.

Measure =
var _avg=CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),YEAR('Table'[dato])=YEAR(MAX('Table'[dato]))&&MONTH('Table'[dato])=1))
var _divide=DIVIDE(_avg,12)
return
IF(YEAR(MAX('Table'[dato]))=YEAR(MAX('Table'[dato])),_divide,_divide)

2. Result.

Is the result in line with your expectations

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Portrek]

Hello,

You can inside the query, click in the column and select the Split Columns and then select By Delimiter option.

After that, put comma ( ,) and last choose the date format.

See you !