Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |