Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
DcAssink
Regular Visitor

Year over year compare of cumulative revenue.

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:

 

example01.jpg

 

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

example02.jpg

 

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.

1 ACCEPTED SOLUTION
DcAssink
Regular Visitor

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:

solution 01.jpg

 

The result I was getting so far:

solution 02.jpg

 

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?

View solution in original post

3 REPLIES 3
DcAssink
Regular Visitor

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:

solution 01.jpg

 

The result I was getting so far:

solution 02.jpg

 

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
Super User
Super User

@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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.