Trailing Twelve Month Revenue

[MAkiva1]

I am seeing a very large discrepancy between the desktop version of Power BI and the online for this field.  I'm not too sure what's causing the discrepancy and I'm looking for help.  Specifics below:

I calculate revenue using three separate fields in a SQL database. I've computed the TTM revenue as follows:

TTM Revenue = CALCULATE(SUM(CUST_ContactTable2_121703[Fee Amount]),DATESINPERIOD(CUST_ContactTable1_085310[INSettDate],TODAY(),-365,DAY),CUST_ContactTable1_085310[INUIMSettDate]=BLANK()) + CALCULATE(SUM(CUST_ContactTable2_121703[Fee Amount]),DATESINPERIOD(CUST_ContactTable1_085310[INUIMSettDate],TODAY(),-365,DAY)) + CALCULATE(DIVIDE((SUM(CUST_ContactTable1_085310[CUST_INSettAmnt_111008586]) + SUM(CUST_ContactTable1_085310[CUST_INUIMSettAmnt_111324801])),3),CUST_ContactTable2_121703[Fee Amount]=BLANK(),DATESINPERIOD(CUST_ContactTable1_085310[INSettDate],TODAY(),-365,DAY))

In the desktop version of Power Bi, this computes accurately.  In the online portal, the number is 1/6 of the desktop result.  Any suggestions as to why?  I'm using DirectQuery and I refreshed the data.  Gateway is working fine, as other fields are updated accurately.

[Reid_Havens]

Hi there,

Glad to offer some help. First off, if you're unfamiliar with DAX Formatter. Great tool to clean up large DAX formulas. I used it to section out your formula to better read it. First question for you, is there a reason you're doing -365 DAYS instead of -1 YEAR?

=
CALCULATE (
    SUM ( CUST_ContactTable2_121703[Fee Amount] ),
    DATESINPERIOD ( CUST_ContactTable1_085310[INSettDate], TODAY (), -365, DAY ),
    CUST_ContactTable1_085310[INUIMSettDate] = BLANK ()
)
    + CALCULATE (
        SUM ( CUST_ContactTable2_121703[Fee Amount] ),
        DATESINPERIOD ( CUST_ContactTable1_085310[INUIMSettDate], TODAY (), -365, DAY )
    )
    + CALCULATE (
        DIVIDE (
            (
                SUM ( CUST_ContactTable1_085310[CUST_INSettAmnt_111008586] )
                    + SUM ( CUST_ContactTable1_085310[CUST_INUIMSettAmnt_111324801] )
            ),
            3
        ),
        CUST_ContactTable2_121703[Fee Amount] = BLANK (),
        DATESINPERIOD ( CUST_ContactTable1_085310[INSettDate], TODAY (), -365, DAY )
    )

[MAkiva1]

No reason.

[Reid_Havens]

I personally use Alberto Ferrari's method of creating a 12-month window for a value. The full article can be found here. But I'll include the formula below. I'd recommend maybe seeing if this works in the Desktop app first, and then on PBI.com.

Sales12M := CALCULATE (
    [Sales],
    DATESBETWEEN (
        Calendar[FullDate],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[FullDate] ) ) ),
        LASTDATE ( Calendar[FullDate] )
    )
)

Quote from his article:

"

The behavior of the formula is simple: it computes the value of [Sales] after creating a filter on the calendar that shows exactly one full year of data. The core of the formula is the DATESBETWEEN, which returns an inclusive set of dates between the two boundaries. The lower one is:

SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( Calendar[FullDate] ) ) ) 

Reading it from the innermost: if we are showing data for a month, say July 2007, we take the last visible date using LASTDATE, which returns the last day in July 2007. Then we use NEXTDAY to take the 1st of August 2007 and we finally use SAMEPERIODLASTYEAR to shift it back one year, yielding 1st of August 2006. The upper boundary is simply LASTDATE, i.e. end of July 2007.

"