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.
this doesn't look too hard, but can you please post the table structure you are using in your model (I assume it is. It what you have posted already.
Cheers, Matt, appreciate the offer of assistance. I'm primarily a finance person with LEAN skills and a degree in using Excel... (joke)! I primarily work in continuous improvement, but dabble with data analysis.
Not sure what you mean by table structure...?
Essentially, I have 750k rows with multiple fields. The key fields are as noted in my illustration, but I want to be able to cut and slice the output by other fields (such as type and sales channel), so:
Product #
Sales Date
Cancellation Date
Status (L or C)
Sales Channel
Prod Type
Brand
etc...
The main challenge is reporting a sale, which has subsequently cancelled (probably in a different period, whether that be week, month or even year), consistently across the outputs, as net = 0, gross = 1, canx = -1.
However, if I am reporting 2016, then if the sale is in 2015, but cancels in 2016, I would report -1 (as the sale belongs to 2015 and is already reported there).
However, if sale was in 2016 and cancelled in 2016, my report output for 2016 would be zero.
At a lower level, we report weekly metrics and I would want to be able to track 'net sales' by week. The problem is that the sales in 'week x' are those with a 'sales date' in week 1, however the canx in 'week x' are those with a cancellation date in 'week x' (which is unlikely to be the same week as the sale...!).
In term sof the data table, the product ('sale') is only recorded in the dataset ONCE, but the status (L= Live, C = Cancelled) determines what to count in the total and the 'sales' or 'cancellation' date the period in which it needs counted.
L = 1 (according to sales date)
C = 1 and -1 (assigned to different dates)
Thanks for any help!
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.
