Forum Discussion

AurelieP's avatar
AurelieP
Frequent Visitor
2 years ago
Solved

Need help with a formula: difference compared to previous quarter when quarters are custom.

I would like to calculate the difference in the number on leads this quarter versus the numbers of leads on the past quarter.

So for instance if last quarter I had 10 leads and this quarter 12 the difference will be 2.

Then I want to use this to see a trend (line chart) over time for the last X quarters. 

The issue I have is our quarters are custom and so I can't use a date table, we are numbering our quarters starting from 1 when we started and each quarter gets a new number. The quarter number is unique and there will never be duplicates.

So for instance now I am in quarter 92 and last quarter was quarter 91. I think I need to somehow use this for the calculation?

 

I am struggling to find how to build the calculation. Any help with this will be really appreciated.

 

Thanks so much in avance

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi AurelieP ,

     

    You can try measure formula like below:

    Result =
VAR cur =
    MAX ( YourTable[Quarter number] )
VAR cur_leads =
    CALCULATE (
        SUM ( 'YourTable'[# Leads] ),
        FILTER ( ALL ( YourTable ), 'YourTable'[Quarter number] = cur - 1 )
    )
VAR difference =
    MAX ( YourTable[# Leads] ) - cur_leads
RETURN
    IF ( cur - 1 <> 0, DIVIDE ( difference, cur_leads ), BLANK () )

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    2 years ago

    in order to give you the best possible solution, please provide some sample data with an example of what you are expecting to see and how?  

    • AurelieP's avatar
      AurelieP
      Frequent Visitor
      2 years ago

      Hello ,

       

      Here is an example, I want to calculate the difference and the variation

      The difference is number of leads in a specific quarter - number of leads 1 quarter before. 

      The variation is ((leads in a specific quarter − leads 1 quarter before) / leads 1 quarter before)×100

      I also put the formulas in the 1st 2 rows above. 

      I know these 2 fields need to be measures and to calculate it I probably need to use the quarter number as a reference and -1 for the previous quarter.. Hope this helps

      Quarter number# LeadsDifferenceVariation
      115  
      22525-15= 10

      ((25-15)/15)*100=

      67%

      3150150-25= 125

      ((150-25)/55)*100=

      500%

      475-75-50%
      512-63-84%
      615325%
      720533%
      8301050%
      910-20-67%
      105040400%
      1125-25-50%
      1215-10-40%
      133015100%
      146030100%
      1514-46-77%
      163521150%
      1720-15-43%
      1818-2-10%
      1910082456%
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi AurelieP ,

         

        You can try measure formula like below:

        Result =
VAR cur =
    MAX ( YourTable[Quarter number] )
VAR cur_leads =
    CALCULATE (
        SUM ( 'YourTable'[# Leads] ),
        FILTER ( ALL ( YourTable ), 'YourTable'[Quarter number] = cur - 1 )
    )
VAR difference =
    MAX ( YourTable[# Leads] ) - cur_leads
RETURN
    IF ( cur - 1 <> 0, DIVIDE ( difference, cur_leads ), BLANK () )

         

        Best Regards,
        Adamk Kong

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly