Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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 th...
Need Help
Tips and Tricks
Ciria's avatar
Ciria
Advocate III
8 years ago

Hi Anonymous

 

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!:smileyvery-happy:

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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:

 

 

The report I want to calculate for each year:

 

 

The relationship I use between the 2 tables:

 

 

Hopefully you can help me with my challenge.

 

Thanks in advance,

 

Cor

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

     

    Report_example2
    Report_example2.JPG1067x521 125 KB

     

    Chrs,

    Cor

    • Ciria's avatar
      Ciria
      Advocate III
      8 years ago

      Hi Anonymous

       

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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:

         

        DAX%20New%20and%20steady%20customers
        DAX New and steady customers.JPG980x663 46.2 KB

         

        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