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.
By table structure I mean what are the columns you have. If they are like you originally posted, how did they end up looking like that? I would not normally expect such a structure unless some other system or tool had restructured the data. For example, you have a 16Sales and 16Canx column. Does hat mean you have 2 columns for 15, 2 for 14, 2 for 13 etc? Also where has the date information gone? Presumably these sales happened on a specific date, and the detail is stored somewhere.
I realise I don't understand the data, but it seems to me that the last 3 columns are superfluous. I can work out the last 3 from the first 3. Is that correct? I would be seeking a table that has product number, sale date (not year) and cancellation date. If the cancellation date doesn't exist, then it hasn't been cancelled.
Would that work?
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 :-)