cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
corvada Helper III
Helper III

Time comparison multiple years

Hello everyone,

I’ve been struggling the past few days with DAX-formulas to compare several years.
On the internet there are several formulas but none of them gives me the result I want.
What’s the problem/challenge?

I’ve got 2 tables:

  1. Calendar;
  2. Model Fct_InvoiceLine, a fact-table with invoicelines from 2014-2018

I want to compare the number of customers and the amount of Sales during the years 2014-2018.

The result in the end:

  • New customers: Number of customers and amount of Sales for each year
  • Steady customers:
  1. Won revenue: number of customers and amount Sales for each year
  2. Steady: number of customers and amount Sales for each year
  3. Lost revenue: number of customers and amount Sales for each year
    A steady customer is a customer with Sales in each year but the new Sales is more, less or the same as the previous year.
  • Lost customers: Number of customers and amount of Sales for each year

My measures:

Total Turnover = SUM (‘Model Fct_InvoiceLine’[InvoiceLineTotalAmountExclVAT]

 

Number of Customers =
CALCULATE (
DISTINCTCOUNT ( ‘Model Fct_InvoiceLine’[Customernumber] );
FILTER ( ALL ( ‘Model Fct_InvoiceLine’[Customernumber] ); [Total Turnover] <> 0 )
)

 

The numbers from the measures mentioned above seems to be right.

 

At this point I need to go further but I’m lost.

I think that I have to make a virtual table with customernumber and sales for each year and after that I want to compare those tables with each other. I thought to use EXCEPT and SELECTEDCOLUMNS but I don’t know exactly how to use them.

 

Can you help me?

 

Thanks in advance,

Cor

8 REPLIES 8
Ciria Advocate III
Advocate III

Re: Time comparison multiple years

Hi @corvada

 

The function you neccesary need is SAMEPERIODLASTYEAR, or even better DATEADD.

With this function you can calculate multiple measures just changing intervals numbers.

 

About how clasiffy your clients, based on sales. You have to create a manual table, and then you can use Switch function to clasify each row or customer or whatever....

EnterpriseDNA has tutorials in youtube, but I cannot find it right now.

 

I've found this one from Curbal, which may help you with Virtual Tables and Summarize Function.

 

https://www.youtube.com/watch?v=-Ola264bKXk

 

Good luck!Smiley Very Happy

corvada Helper III
Helper III

Re: Time comparison multiple years

Hi @Ciria,

 

Thanks for your reply!

 

I've tried and tried and uptil now I haven't found a solution. I've made a dummy model and hopefully you can help me to count the customers for each year and the Sales.

 

The data:

 

Invoices_example.JPG

 

The report I want to calculate for each year:

 

Report_example.JPG

 

The relationship I use between the 2 tables:

 

Relationships_example.JPG

 

Hopefully you can help me with my challenge.

 

Thanks in advance,

 

Cor

 

corvada Helper III
Helper III

Re: Time comparison multiple years

I’ve made a mistake in the report: the lost revenues % # Customers was wrong. The correct report should be:

 

 

Chrs,

Cor

Highlighted
Ciria Advocate III
Advocate III

Re: Time comparison multiple years

Hi @corvada

 

Yesterday I was precisely watching a video in youtube about how manage a situation like yours.

 

https://www.youtube.com/watch?v=SOkBdbu4sVc&list=LLECfzwATlNAWnYdkkGLenGw&index=3&t=50s

 

There are three parts, but your model looks definately simpler than her one.

 

Take a look it and let me know if it works.

 

DISTINTCOUNT looks definately the formula you need. A Both direction relathionships I think is not necessary, single side could be ok.

 

Good Luck!

corvada Helper III
Helper III

Re: Time comparison multiple years

Hi @Ciria,

 

Thanks for your reply, weekly I see the Curbal DAX-Friday-videos, Ruth does a good job. 🙂

 

In the meantime I’ve made the DAX-formulas for New and Steady customers:

 

 

Can you help me with the formula for Lost customers? Now I have the numbers for New and Steady customers, how can I connect these numbers with the amount of Sales?

 

Thanks in advance,

 

Cor

Ciria Advocate III
Advocate III

Re: Time comparison multiple years

Hi @corvada

 

I'm afraid of without having the model I cannot help you more, additionally your DAX looks pretty advanced, so I am not sure if I will be able to help you any further....

 

About your question, maybe this video helps you to clasify your customers....

 

https://www.youtube.com/watch?v=Nboy_u8Iv74&index=2&list=LLECfzwATlNAWnYdkkGLenGw

 

Keep me posted if it works!

corvada Helper III
Helper III

Re: Time comparison multiple years

Hi @Ciria,

 

Thanks for your reply!

 

Sam McKay makes wonderful videos and the video you mentioned is also an excellent video by Sam but the video doesn't help me to answer my questions. I'm still looking for a solution to connect the number of lost-steady and new customers to the Sales-amount and compare these figures with the figure of the previous year.

 

Is there someone who can help me?

 

Thanks in advance,

 

With kind regards,

 

Cor

 

Ciria Advocate III
Advocate III

Re: Time comparison multiple years

Hi @corvada

 

What about the use of LASTNONBLANK??

 

This function may return the last date with sales for every customer. I guess adding a IF function you could classify every customer.

I guess a customer with no sales on the present year, may be classifed as "lost customer"?

 

Regards,

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors