cancel
Showing results for
Did you mean:

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.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors