Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to calculate the revenue per month using data like this
Project StartDate EndDate TotalRevenue Proj1 2018-01-01 2018-12-31 1200
So I can get SUM per month.
In a best case scenario the data would already look something like this:
Project Date Revenue Proj1 2018-01-01 100 Proj1 2018-02-01 100 Proj1 2018-03-01 100
Solved! Go to Solution.
HI @Anonymous
You can try using this calculated Table in DAX
From the Modelling Tab >>>NEW TABLE
Table = VAR temp = ADDCOLUMNS ( GENERATE ( Table1, GENERATESERIES ( MONTH ( Table1[startdate] ), MONTH ( Table1[enddate] ) ) ), "No_of_Months", DATEDIFF ( Table1[startdate], Table1[enddate], MONTH ) + 1 ) RETURN SELECTCOLUMNS ( temp, "Project", [project], "Month", EOMONTH ( [startdate], [Value] - 2 ) + 1, "MonthlyValue", [totalrevenue] / [No_of_Months] )
Hi,
You may refer to my solution in the PBI file here. Click on the Data tab on the left hand side
Hi,
You may refer to my solution in the PBI file here. Click on the Data tab on the left hand side
HI @Anonymous
You can try using this calculated Table in DAX
From the Modelling Tab >>>NEW TABLE
Table = VAR temp = ADDCOLUMNS ( GENERATE ( Table1, GENERATESERIES ( MONTH ( Table1[startdate] ), MONTH ( Table1[enddate] ) ) ), "No_of_Months", DATEDIFF ( Table1[startdate], Table1[enddate], MONTH ) + 1 ) RETURN SELECTCOLUMNS ( temp, "Project", [project], "Month", EOMONTH ( [startdate], [Value] - 2 ) + 1, "MonthlyValue", [totalrevenue] / [No_of_Months] )
Thank you both! Working now.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |