Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dinovic
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
v-sihou-msft
Employee
Employee

@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

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.