cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Revenue month to month

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```

2 ACCEPTED SOLUTIONS
Community Champion

HI @Anonymous

You can try using this calculated Table in DAX

From the Modelling Tab >>>NEW TABLE

```Table =
VAR temp =
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]
)
```

Regards
Zubair

Super User

Hi,

You may refer to my solution in the PBI file here.  Click on the Data tab on the left hand side

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

Hi,

You may refer to my solution in the PBI file here.  Click on the Data tab on the left hand side

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

HI @Anonymous

You can try using this calculated Table in DAX

From the Modelling Tab >>>NEW TABLE

```Table =
VAR temp =
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]
)
```

Regards
Zubair

Anonymous
Not applicable

Thank you both! Working now.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.