Split average of sum between start and end date

[chrismpbi]

Hi,

I would like to calculate the average of a sum and split it between the start and end date.

Hope somebody can help me with this. Chris

[v-yulgu-msft]

Hi @chrismpbi,

Suppose the dataset is look like below. If there isn't a [No] column to identify each row uniquely, you should add an index column in Query Editor mode first.

Create a calendar table.

Dim Date =
FILTER (
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Test Data1'[Start Date] ), MAX ( 'Test Data1'[End Date] ) ),
        "Month", FORMAT ( [Date], "MMM" )
    ),
    DAY ( [Date] ) = 1
)

New a calculated table to CrossJoin above two tables. And add a calculated column [Average Per month]

CrossJoin =
FILTER (
    CROSSJOIN ( 'Test Data1', 'Dim Date' ),
    'Dim Date'[Date] >= 'Test Data1'[Start Date]
        && 'Dim Date'[Date] <= 'Test Data1'[End Date]
)

Average per month =
'CrossJoin'[Amount]
    / CALCULATE (
        COUNT ( 'CrossJoin'[Month] ),
        ALLEXCEPT ( 'CrossJoin', 'CrossJoin'[No] )
    )

Use a Matrix to analyse data.

Best regards,

Yuliana Gu

[chrismpbi]

Hi Yuliana,

thanks for your response! It seems to work but the month by which the amount is divided should be independent from the date filters. in the screenshot below the amount is just divided through the month that are in the selected year.

[jthomson]

There's a few posts on how to do this, but a bit more info would be useful - are you just splitting it by months? Does it matter how much of a month a project is running for? What were you doing in May?

[chrismpbi]

Hi Jthomson,

This is just an example dataset. I have a few thousand lines with project costs. I would like to split those costs over the project duration. It would be good if I could also split it per day but its not necessary.

Christoph