Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |