Allocation of Amount into its week using start and end booking date.

[Anonymous]

I have amounts. A start date and an end date.

I want to allocate the amounts into their weeks (red cells)

I can do this in excel using if statements. Can we do this in Power BI?

Thanks Team

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create two calculated tables

One is "date" table

date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"week",WEEKNUM([Date],1))

Add a caluclated column in this table

weeknum = CALCULATE(MIN([Date]),ALLEXCEPT('date','date'[year],'date'[week]))

Another table 

Table 2 =
FILTER (
    CROSSJOIN (
        'Table',
        FILTER (
            'date',
            'date'[Date] <= CALCULATE ( MAX ( 'Table'[end date] ), ALL ( 'Table' ) )
                && 'date'[Date] >= CALCULATE ( MIN ( 'Table'[start date] ), ALL ( 'Table' ) )
        )
    ),
    [Date] >= [start date]
        && [Date] <= [end date]
)

Create a measure in this table

Measure = CALCULATE(SUM('Table 2'[amount per day]),ALLEXCEPT('Table 2','Table 2'[prepayment],'Table 2'[year],'Table 2'[weeknum]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Maggie! You are so smart, it works!

I've seen your responses on other things i have searched up and all have been so good. How did you get so smart?

But just a query, what is the purpose of the measure? Couldn't I still produce the same matrix without using measure but instead amount per day?

[v-juanli-msft]

Hi @Anonymous 

In Power BI, we need to create another table, it would be static, would you like this method?

Best Regards
Maggie

[Anonymous]

That would be good. Just as long as we can acheive the same output.