Forum Discussion
Reporting Net Sales - Gross less Canx
Import the first 3 columns into the data model
create a lookup table that contains all possible years for your data (sales and Canx) and add to the datamodel
join the data[sales year] column to the lookup table year
join the canx year from the data table to the lookup table year (inactive relationship)
add a table to the canvas, and put year from the lookup table on the values
write the the following measures.
Gross sales = countrows(dataTable)
total cancelations = calculate(countrows(dataTable),userelationship(lookupTable[year],dataTable[year]))
net sales = [Gross Sales] - [Total Cancellations]
add the measures you want to the table.
Correct!
The last two columns were informational only and do not exist in the actual dataset.
The Status column does exist in the datset, but you are right that (from a data perspective) the C status can be derived where 'cancel date' <> BLANK.
However, the way the (in-house legacy) system works, is that the status is first set to 'cancelled' then the user requires to enter a cancellation date.
I listed the key 'table structure' fields in my last post.
Cheers.
Import the first 3 columns into the data model
create a lookup table that contains all possible years for your data (sales and Canx) and add to the datamodel
join the data[sales year] column to the lookup table year
join the canx year from the data table to the lookup table year (inactive relationship)
add a table to the canvas, and put year from the lookup table on the values
write the the following measures.
Gross sales = countrows(dataTable)
total cancelations = calculate(countrows(dataTable),userelationship(lookupTable[year],dataTable[year]))
net sales = [Gross Sales] - [Total Cancellations]
add the measures you want to the table.
Genius, Matt! Thanks a lot, mate. I owe you a beer.
My datasets were a bit more complicated than illustrated, but I worked out how to apply your formulae to link specific dates and report out 'net sales' easily for the very first time by various slices, including product type, sales channel, 'week #' and accounting periods.
An exercise that would have required HOURS of effort beforehand, using Excel and adding gross and cancels toegther manually...
One very happy Finance team!
Cheers.
Glad you worked it out from here. Once you get started, there is no stopping :-)