Apportioning values on Power BI by months

[Anonymous]

Hi,

Specific issue with trying to apportion gas usage data by month. 

I have the date/previous data and the total consumption.

For example 12/09/18 to 21/10/18 and total consumption of 25,000.

I want to get this 25,000 to be apportioned by a daily average and assigned to a month.

I.e. 21/10/18-12/09/18 = 30

25,000/31 = 833.3333

12/09/18 to 30/09/18 = 9

01/10/18 to 21/10/18 = 21

Therefore, I am looking to allocate 9*833.3333 to Sep 18 and 21*833.3333 to Oct 18.

Can anyone help with this specific problem?

Thanks, 

Ben

[v-juanli-msft]

Hi @Anonymous

Create a measure which consist of many measures

(the table on the left show how the measures work step by step)

allocation2 =
VAR total =
    CALCULATE ( SUM ( Sheet3[value] ), ALLSELECTED ( Sheet3 ) )
VAR day_count =
    CALCULATE ( COUNT ( Sheet3[date] ), ALLSELECTED ( Sheet3 ) )
VAR average_daily = total
    / ( day_count - 1 )
VAR mindate =
    CALCULATE (
        MIN ( Sheet3[date] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[Year] = MAX ( Sheet3[Year] )
                && Sheet3[Month] = MAX ( Sheet3[Month] )
        )
    )
VAR maxdate =
    CALCULATE (
        MAX ( Sheet3[date] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[Year] = MAX ( Sheet3[Year] )
                && Sheet3[Month] = MAX ( Sheet3[Month] )
        )
    )
VAR day_month =
    DATEDIFF ( mindate, maxdate, DAY )
VAR min_month =
    CALCULATE (
        MIN ( Sheet3[Month] ),
        FILTER ( ALLSELECTED ( Sheet3 ), Sheet3[Year] = MAX ( Sheet3[Year] ) )
    )
VAR day_month_correct =
    IF ( MAX ( Sheet3[Month] ) = min_month, day_month, day_month + 1 )
RETURN
    average_daily * day_month_correct

Best Regards

Maggie