cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dinovic Frequent Visitor
Frequent Visitor

Calculate revenue per day on Cumulative Revenue Data

I have a Sales table.

Product, Revenue, Date
Car, 500, 11/12/2016
Bike, 400, 11/12/2016
Plane, 1000, 11/12/2016
Car, 900, 12/12/2016
Plane, 2500, 12/12/2016

Bike, 700, 12/12/2016

The revenue data is displayed on a cumulative basis. What I want is an additional calculated column that lets me calculate revenue per day for each of the products. Like this

Product, Revenue, Date, Daily Revenue
Car, 500, 11/12/2016, 500
Bike, 400, 11/12/2016, 400
Plane, 1000, 11/12/2016, 1000
Car, 900, 12/12/2016, 400 (900 - 500)
Plane, 2500, 12/12/2016, 1500 (2500-1000)
Bike, 700, 12/12/2016, 300 (700 - 400)

The date format will always be continous daily data. Please note that the products dont always appear in the same order, and in the real dataset there is 150 or so different products.. So I have to pass an argument to filter out the correct rows for each and every product.

This task is easy in excel. However, I cant seem to write a proper DAX syntax that will make this calculation work as an additional column in Power Bi.

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Calculate revenue per day on Cumulative Revenue Data

@dinovic

 

In this scenario, you can sort your rows based on Product and Cumulative Revenue first. Then add an index column for this sorted table and get the previous Revenue on each row group on Product. Please refer to steps below:

 

1. Sort the source table on Product and Revenue.

12.PNG

 

23.PNG

 

2. Add an index column.

34.PNG

 

3. Then you can create a calculated column to get the Previous Revenue.

 

Previous Revenue = LOOKUPVALUE(Table2[Revenue],Table2[Index],Table2[Index]-1,Table2[Product],Table2[Product])

45.PNG

 

 

4. Use the Cumulative Revenue minus the Previous Revenue.

 

56.PNG

 

Regards,

Simon Hou

2 REPLIES 2
Super User
Super User

Re: Calculate revenue per day on Cumulative Revenue Data

There are a few ways to handle this but I wouldn't use a calc column.  I think the best way is to convert the data to daily data on load.  This is complicated to explain so I have produced a sample Excel workbook.  (Same works for Power BI)

 

https://dl.dropboxusercontent.com/u/30711565/convert%20cum%20total%20to%20daily%20total.xlsx 

 

In short, I do the following

1 add a new column with yesterday's date.

2 join the table to itself using the product and the date columns

3 bring in yesterday's sales into the table

4 subtract today from yesterday

5 then you can delete all the interim columns.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Moderator v-sihou-msft
Moderator

Re: Calculate revenue per day on Cumulative Revenue Data

@dinovic

 

In this scenario, you can sort your rows based on Product and Cumulative Revenue first. Then add an index column for this sorted table and get the previous Revenue on each row group on Product. Please refer to steps below:

 

1. Sort the source table on Product and Revenue.

12.PNG

 

23.PNG

 

2. Add an index column.

34.PNG

 

3. Then you can create a calculated column to get the Previous Revenue.

 

Previous Revenue = LOOKUPVALUE(Table2[Revenue],Table2[Index],Table2[Index]-1,Table2[Product],Table2[Product])

45.PNG

 

 

4. Use the Cumulative Revenue minus the Previous Revenue.

 

56.PNG

 

Regards,

Simon Hou