Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Month over Month Lead % Calculation

Hi!

 

I'm trying to calculate the % of leads that move to the next stage from month to month.  I'm hoping to see something like the grid below.  So % of leads that moved from stage 1 to stage 2 in Jan = 30%,  from stage 2 to stage 3 in Jan = 25% etc.  If the lead regresses to a previous stage, do not count.

 

Any help is appreciated!

 

            Month
Stage Jan Feb Mar
2        30% 15% 20%
3        25% 23% 25%
4        10% 30% 10%
5        25% 40% 60%

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous,

    You can try to use the following formula if it is suitable for your requirement:

    measure =
VAR currStage =
    MAX ( Table[Stage] )
RETURN
    IF (
        currStage > 1,
        DIVIDE (
            CALCULATE (
                SUM ( Table[Amount] ),
                FILTER ( ALLSELECTED ( Table ), [Stage] = currStage-1 ),
                VALUES ( Table[Month] )
            ),
            CALCULATE (
                SUM ( Table[Amount] ),
                FILTER ( ALLSELECTED ( Table ), [Stage] = currStage ),
                VALUES ( Table[Month] )
            )
        )
    )

    If the above does not help, can you please share some dummy data? It should help us clarify the data structure and test to coding formula.

    How to Get Your Question Answered Quickly
    Regards,

    Xiaoxin Sheng

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    4 years ago

    Hey Anonymous ,

     

    this article explains almost everything about time / date related calculations using DAX: https://www.daxpatterns.com/time-patterns/

    The most important part is to use a dedicated calendar table.

     

    Next to that, using set functions like functions like INTERSECT or EXCEPT are necessary to determine the number orf leads that are in one month but not in the other.

     

    If that does not help to tackle your challenge, please take the time to create a pbix file that contains sample data and represents your data model (relationships between tables and measures). Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of using the manual input method, share the xlsx as well. Describe the expected result based on the sample data.

     

    Regards,

    Tom

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous,

    You can try to use the following formula if it is suitable for your requirement:

    measure =
VAR currStage =
    MAX ( Table[Stage] )
RETURN
    IF (
        currStage > 1,
        DIVIDE (
            CALCULATE (
                SUM ( Table[Amount] ),
                FILTER ( ALLSELECTED ( Table ), [Stage] = currStage-1 ),
                VALUES ( Table[Month] )
            ),
            CALCULATE (
                SUM ( Table[Amount] ),
                FILTER ( ALLSELECTED ( Table ), [Stage] = currStage ),
                VALUES ( Table[Month] )
            )
        )
    )

    If the above does not help, can you please share some dummy data? It should help us clarify the data structure and test to coding formula.

    How to Get Your Question Answered Quickly
    Regards,

    Xiaoxin Sheng