Revenue month to month

[Anonymous]

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

[Zubair_Muhammad · Accepted 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]
    )

[Ashish_Mathur · Accepted solution]

Hi,

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

[Zubair_Muhammad · Accepted 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]
    )

[Anonymous]

Thank you both! Working now.