Forum Discussion

hedin123's avatar
hedin123
Frequent Visitor
5 years ago

Distributing value equally between months

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! 😄 

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Portrek's avatar
    Portrek
    Resolver III
    5 years ago

    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 !

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      hedin123
      Frequent Visitor
      5 years ago

      Hello Anonymous  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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.