Calculating P12M data but excluding blank months

[joyceleeyw]

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?

[v-frfei-msft · Accepted solution]

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]

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]

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]

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]

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]

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

[joyceleeyw]

Yup! 

[Nathaniel_C]

@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]

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

[Nathaniel_C]

@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

[joyceleeyw]

didnt work 😞 .. i think there's something wrong here - SUMX (LD_PBI, IF(LD_PBI[[VALUE % SHARE]]>0,1) 

I tried using filter instead of if but the formula calculated for all months instead of within the specified period (12 months)

[Nathaniel_C]

Hi @joyceleeyw 

SUMX (LD_PBI, IF(LD_PBI[Value % Share]>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]

Can you include more period? i.e. from jan 17 to jan 19. the denominator has to still be 9 for a P12M average

[Nathaniel_C]

@joyceleeyw ,

Please be more explicit. What are you trying to do?

[joyceleeyw]

I need the denominator to only consider the P12M period when doing the SUMX 

i thought of a measure like this. however, i think there's an error... are you able to help resolve the problem with my formula? 

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

[Nathaniel_C]

@joyceleeyw ,

As this is really a new issue, you would do better by posting it on the forum. Good Luck!

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

[Nathaniel_C]

Based on your original question, did my solution work for you?

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

[joyceleeyw]

Hi @Nathaniel_C 

MY data contains date from jul 2017 - jul 2019, more than 2 years. Which part of the formula tells power bi to do it for just 12 months and not the full 2 years? 

Also cause cause i need to do a 3 months and 6 months average as well with the same data set

[Nathaniel_C]

Hi @joyceleeyw ,

So it looks like DATESINPERIOD(LD_PBI[Date],LASTDATE(LD_PBI[Date]),-12, is giving you a running total over the last 12 months, is that correct?

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

[joyceleeyw]

Hi @Nathaniel_C ,

Can you help give me the full dax measure to test it out plz?