Spilt Revenue By Percentages in right year buckets in Power BI

Question

I have a list of product with revenues across 3 years. However I would like to split the revenue by 60 % and 40% for each year.

For instance:

• Book -> 2020 -> 15mil
• Book -> 2021 -> 18mil
• Book -> 2022 -> 12mil

Therefore in Power BI the revenue should appear as:

• 2020 -> 60% x 15
• 2021 -> 40% x 15 + 60%*18
• 2022 -> 40% x 18 + 60% x 12
• 2023 -> 40% x 12

How can I code this in Power BI to ensure that the revenue are split accordingly?

Update:

I have tried to do a 60% and 40% split column and additional column of Year +1. How do I add them up on Power BI based on year?

Answer 1

I assumed some data model like the following:

[SalesAmt] = SUM(paid_price)

If we now create a table out of the year and [SalesAmt] we get the following. (My sales values were created for testing purposes)

Now for my understanding: You want to display the SalesAmt (which exact Measure doesn't matter) multiplied by 60% and add the SalesAmt from the previous year multiplied by 40%.

For this we can use the DAX formula SAMEPERIODLASTYEAR(), it takes one argument, the date-field for which you want to retrieve the last year's pendant.

[SalesAmt Buckets v1] = 
    SALES[SalesAmt] * 0.6
    + CALCULATE(SALES[SalesAmt], SAMEPERIODLASTYEAR(DATES[fq_date])) * 0.4

Explanation to this formula:

We calculate the current year's [SalesAmt] and multiply it by 60%, then we calculate [SalesAmt] and changing the filter context for this calculation by CALCULATE(). At the end we multiply by your 40% and return the result.

Notice how it is less for the firs year (2019), that's because we don't have a previous year for the row context year 2019, so we just retrieve the current year's value times 40%. For the next years it's correct being 2020 = 40% * 79 + 60% * 95 = 85.