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.

     

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
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.