Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
2. Add an index column.
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])
4. Use the Cumulative Revenue minus the Previous Revenue.
Regards,
Simon Hou
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.
2. Add an index column.
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])
4. Use the Cumulative Revenue minus the Previous Revenue.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |