Forum Discussion

joyceleeyw's avatar
joyceleeyw
Frequent Visitor
6 years ago
Solved

Calculating P12M data but excluding blank months

Hi, I need a dax measure to help me calcuate the average of past 12M however, to only calculate preiod that is not empty. For example, I have sales data from Jan to Dec. My total sals revenue is 1000, however, Jan to Mar is blank. Hence, the formula should automatically detect that there are only 9 months worth of data and hence 1000/9 instread of 1000/12. 

 

Does anyone how a formula that can audotmatically help do the measure calcualtion please?

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi joyceleeyw ,

     

    One sample for your reference, please check the following steps as below.

    1. Create a calculated column in the fact table.

    YM = FORMAT('Table'[date],"yyyymmmm")

    2. After that, we can create measures as below to get P12 or P6 average.

    average p12M = 
VAR A =
    MAX ( 'Table'[date] )
VAR p12 =
    EDATE ( A, -12 )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A )
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[YM] ),
            FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A )
        )
    )

    average p6M = 
VAR A =
    MAX ( 'Table'[date] )
VAR p12 =
    EDATE ( A, -6 )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A )
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[YM] ),
            FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A )
        )
    )

     

    For more details, please check the pbix as attached.

     

     

21 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi joyceleeyw ,

     

    Try Average = DIVIDE ([Total Sales Revenue], SUMX (Table, IF(Table[SalesRevenue]>0,1))

     

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    • joyceleeyw's avatar
      joyceleeyw
      Frequent Visitor
      6 years ago

      Hi Nathaniel,

       

      Sorry i wasnt clear in my question

       

      The current formula i am using to calculate my P12M average is 

      CALCULATE(SUM(LD_PBI[VALUE % SHARE]),DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12,MONTH))/12

       

      However, i need a measure that when jan-mar is empty, to automatically calculate 9 months average (sum all and divide by 9) instead of 12 which what my formula is currently doing

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Hi joyceleeyw ,

        So no problem. 

        SUMX (Table, IF(Table[SalesRevenue]>0,1) replaces your 12. Table is the name of table in your case it seems to be LD_PBI

        So SUMX (LD_PBI,[Whatever column that has your sales revenue]>0,1) just asks whether there is any revenue for that month, if there is, it gives you a 1. Then it sums those up, and works for your denominator.  It can be any month missing or none it will always give you the proper value.

         

        One other thing, you are using "/", instead you might use the DIVIDE () as it doesn't create a problem in divide by zero...also known as the "safe" divide. DIVIDE(numerator,denominator)

         

        If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
        Nathaniel

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi joyceleeyw ,

     

    One sample for your reference, please check the following steps as below.

    1. Create a calculated column in the fact table.

    YM = FORMAT('Table'[date],"yyyymmmm")

    2. After that, we can create measures as below to get P12 or P6 average.

    average p12M = 
VAR A =
    MAX ( 'Table'[date] )
VAR p12 =
    EDATE ( A, -12 )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A )
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[YM] ),
            FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A )
        )
    )

    average p6M = 
VAR A =
    MAX ( 'Table'[date] )
VAR p12 =
    EDATE ( A, -6 )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[value] ),
            FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A )
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[YM] ),
            FILTER ( 'Table', 'Table'[date] >= p12 && 'Table'[date] <= A )
        )
    )

     

    For more details, please check the pbix as attached.