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

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

    • joyceleeyw's avatar
      joyceleeyw
      Frequent Visitor
      7 years ago

      hmm.. there's a problem with the measure ): yes, let's connect again tmr :)

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

        joyceleeyw ,

        I see...the If dropped off with all the copy and paste. Try this:

         

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

         

        The blue is your original (check it for typos.) The red - after the "/" is the new portion and replaces the "12"

         

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