Need help with creating pipeline report by months

[andrispajula]

Hello,

I am very very new to PowerBI so my knowledge about it (and programming in general) is very limited. What I am trying to do is create a pipeline report.

I am exporting data from a excel file. This file contains 4 fields(on one sheet). Name; From; To; Cost.

What I need to do is calculate how many months is between from and to. After that divide cost with the number of months in a year. So that it divides the full cost between all the months it was active.

For an example:
PROJECT1  01.01.16  01.06.16(june) 12000     

So i need to divide 12000 with 5 months.

What I need to get is: 
NAME;      From;          To;              Cost;      01.01.16;   01.02.16;   01.03.16;   01.04.16.    01.05.16;     01.06.16   01.07.16.   01.08.16;
   Prj1       01.01.16    01.06.16       12000        2400         2400         2400         2400           2400            

Lets say Project2 has the following data:
PROJECT2     01.04.16    01.07.16     18000

The results should be:

NAME;      From;          To;              Cost;      01.01.16;   01.02.16;   01.03.16;   01.04.16.    01.05.16;     01.06.16    01.07.16   01.08.16;
   Prj1       01.01.16    01.06.16       12000        2400         2400         2400         2400           2400            

   Prj2       01.04.16    01.07.16       18000                                                           6000           6000            6000


Im sure that this is really easy but like i stressed before, I am a rookie regarding this matter.

Thanks you in advance, any help is appriciated.

[Anonymous · Accepted solution]

Hi @andrispajula,

Firstly, create a calendar table in your current Power BI Desktop file following the guide in this blog.

Secondly, create the following two columns and measure in your table, please  note that there is  no relationship between calendar table and your table.

A new column to get the total number of months.

NumMonths = DATEDIFF(Table1[From], Table1[To],MONTH)

A new column to get cost by month.

CostByMonth = DIVIDE(Table1[Cost], Table1[NumMonths])

A measure to get the desired value.

Measure 4 = CALCULATE(SUM(Table1[CostByMonth]), 
                     FILTER(Table1
                            , (Table1[From] < LASTDATE('Date'[Date]) 
                              && Table1[To]> FIRSTDATE('Date'[Date])
                              )
                           )
                    )

Create a Matrix as shown in the screenshot below.


Thanks,
Lydia Zhang