Forum Discussion

joyceleeyw's avatar
joyceleeyw
Frequent Visitor
7 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...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 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.

     

     

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
7 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

joyceleeyw's avatar
joyceleeyw
Frequent Visitor
7 years ago

Please use [VALUE % SHARE] 

 

I want to find the average value share over a period of 12 months

 

Table name - LD_PBI

Date column name - LD_PBI[Date]

Value share column name - LD_PBI[VALUE % SHARE]

 

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

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Is [Value %  Share] have a monthly amount for your table?

     

     

    • joyceleeyw's avatar
      joyceleeyw
      Frequent Visitor
      7 years ago

      Yup! 

      • Nathaniel_C's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        7 years ago

        joyceleeyw 

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

        This just replaces your 12 with my formula.

        You could test the SUMX (LD_PBI,[VALUE % SHARE]>0,1) as a measure and place it in a card in Power BI.

        Signing off, but will check in tomorrow.

        It also looks like your original formula if you replace the 12 (both times) with 3 or 6 will give you the average over the last 3 or 6 mo.

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