- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Year over year compare of cumulative revenue.

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Year over year compare of cumulative revenue.

06-28-2017
07:15 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-29-2017
04:10 AM

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?

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-29-2017
04:10 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-28-2017
07:59 AM

@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

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Announcements

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

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

117 | |

109 | |

109 | |

93 | |

69 |

Top Kudoed Authors

User | Count |
---|---|

173 | |

135 | |

131 | |

96 | |

94 |