Year over year compare of cumulative revenue.

[DcAssink]

I have a couple of tables in my model.

• Obviously I have a date table containing your typical fields like Date. (my dates table runs until 31-12-2017)
• I have a customer table with a CustID, Name etcetera.
• I have a table with invoices. A small sample of data below:

I want to create a table (or matrix) that essentially shows the information below.

For each customer, for each year, I want to show the running total invoiced, but only up until today.

I do not need the TOT 2016 column in my visual. I have added it here for information purposes only.

• RT17 should show my running total invoiced (for each customer) for 2017. So the sum of all invoices in 2017 until today.
• RT16 should show my running total invoiced (for each customer) for 2016. 

Assume today is 06-06-2017, then RT16 should be calculated as the sum of all invoices from 01-01-2016 until 06-06-2016.

I have created a number of measures to achieve this result, but none seem to work for me. (All of them do calculate RT17 correctly, but I am strugelling to get my measure correct for previous years. They always seem to calculate the total over 2016 and never only part of the year.

I would think this measure should work, but.... ehhhh... nope... (placing the measure in the Customer table.

RT = CALCULATE ( SUM ( 'Invoice'[Amount] ); FILTER ( 'Invoice'; 'Invoice'[Postingdate] <= 'DateTable'[Date]

                                                                                      && 'Invoice'[Postingdate] >= STARTOFYEAR ( 'DateTable'[Date] ) ) )

I would think this should work and it works fine for 2017, but not for previous years.

[DcAssink · Accepted solution]

I found a solution that works and even works year-over-year-over-year-over-year

First I have created an extra column on my Invoice table with the following formula:

InvoiceDayNBR = 'Invoice'[Postingdate] - DATE( YEAR( 'Invoice'[Postingdate]); 1; 1) +1

This gives me the daynumber in the current year.

Next I created the following measure on my customer table:

RT = CALCULATE( SUM( 'Invoice'[Amount]);

        FILTER( 'Invoice'; ( TODAY() - DATE( Year( TODAY() ); 1; 1) +1) > 'Invoice'[InvoiceDayNBR]) )

Adding this measure to a matrix with the customer table[name] as rows and DateTable[Date] (years only) as colums, I get the following result:

The result I was aiming for:

The result I was getting so far:

Notice that in the correct result, there is no value for 2016. This is correct, since all sales to customer ADC is in october and november of 2016. There is also slight variations in 2010 and 2011, since in those two years there are sales in the last months of the year.

Still my solution feels very clunky and I would need to create a different measure to get this working for months as well. So if someone would be able to provide a more felxible solutions, I am all ears.

You may wonder, why did he not just use the STARTOFYEAR function to calculate the first day of the year?

STARTOFYEAR( 'Invoice'[Postingdate] ) gives me the first postingdate in the year of postingdate, which nearly never is 1-1-####. For instance in 2017 my first posting is on January 2nd, so for any postingdate in 2017, I get 02-01-2017 as the first day of the year. In 2015, my first posting is on March 15th, so for any postingdate in 2015, STARTOFYEAR returns 15-03-2105.

Which brings me to a second question: Is this how STARTOFYEAR is supposed to work? Which function should I be using to determine the first of january of a given year based on a random date?

[vanessafvg]

@DcAssink i am little confused as to what you trying to say exactly 

it really easier to understand if you provide an example of the problem in a visual and them provide what you expect in an example

I might be off the mark but i think you might need to use an iterator, such as sumx (as opposed to sum)

however if you provide the above , i can try and assist

[DcAssink]

@vanessafvg I have rewritten my post. I hope it is a little clearer.