Measure total does not return correct value

[Anonymous]

Hi all,

I am stuck with a problem regarding the calculation of a month revenue.

We work on project basis, which means I have made a measure calculating the total revenue of a project divided by the month that it spans over and then distributing that revenue to the respective months. (fx: January start, February finish: 50.000 Jan, 50.000 Feb)

However, when I use SUMX or CALCULATE([MonthlyRevenue], InBetween(Calendar[Date], Date(2019,9,1),[End of Last Month]) it returns a wrong value.

My measure for the monthly revenue is quite long, it looks like this (this works correctly, so I am not asking for help regarding its function, but maybe it will help us understand why the correct sum of the total revenue is returned):

Monthly Revenue= 
SUMX (
    ADDCOLUMNS (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"          && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] >= MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] <= MIN ( 'Calendar'[Date] )
                )
        ),
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

Please let me know if I can provide any other information regarding the measures etc.

[ImkeF · Accepted solution]

Hi @Anonymous ,

if your monthly revenue table has multiple lines per project, you might have to summarize the filtered table on the project level before creating the average. Otherwise the average won't add up on project level. (The value of the total "field" is calculated separately, so you need to create a virtual table that contains the project level)

Monthly Revenue=
SUMX (
    ADDCOLUMNS (
    SUMMARIZE(
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"          && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] >= MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] <= MIN ( 'Calendar'[Date] )
                )
        ),
                    'Monthly Revenue Table'[Project ID]     
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time]
),
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

Sorry about the formatting.. 

[Anonymous]

hi @ImkeF 

Thank you for the response.

I am not able to use the measure you wrote as it does not accept the part ""AverageBudget", 'Monthly Revenue Table'[Current Client Budget]" as seen below:

When I try to rewrite that part, it seems it only accepts measures. What am I doing wrong?

[ImkeF]

Hi @Anonymous 

sorry, I didn't pay attention to that part.

You'll need an aggregation at that stage that you have to wrap in a CALCULATE (due to the row context, that ADDCOLUMNS creates).

"Average Budget", CALCULATE(SUM( ....

Of course you could create those as measures separately and use in there if you prefer that.

[Anonymous]

@ImkeF 

Unfortunately it still shows the exact same number (667k) whereas it is supposed to show (1.038k). Using the measure you created and the original one gives the same values when placing them in a simple graph form:

[ImkeF]

Hi @Anonymous 

yes, I was missing the monthly part here.

Actually, not sure about the desired calculation logic here. As the projects can span multiple months, how shall the average on project-level across multiple months actually work?
Could you please create some mockup data that allow to reconcile the desired result?

[Anonymous]

@ImkeF,

Sorry if I have been confusing in my explanation. In the above screenshot I posted you can see the "Earned Revenue Monthly", which showcases the revenue for each month, based on all ongoing projects for those months. Ideally, I would simply like to add those up, so that I can use it for a measure I have created, which basically calculates the total revenue over a date period.

Total Revenue for All (Start to last month) = CALCULATE([Monthly Revenue],DATESBETWEEN('Calendar'[Date], DATE(2019,9,1),[End of Last Month]))

I need the correct summarized sum of the months October 2019-January2020 as can be seen below:

[ImkeF]

Hi @Anonymous ,

you can try to add the month-column to the SUMMARIZE-fields.

If that doesn't work, I can only help you if you provide sample data.