CODEWITHSUNDEEP
powerbipowerbi-desktopfinancerevenue
milkywaypowerbi
milkywaypowerbi

Reputation: 1

Spilt Revenue By Percentages in right year buckets in Power BI

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:

Therefore in Power BI the revenue should appear as:

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?

Upvotes: 0

Views: 472

Answers (1)

KaiE
KaiE

Reputation: 16

I assumed some data model like the following: PowerBi datamodel

[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)

data table v1

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.

data table v2

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.

Upvotes: 0

Related Questions