Analyze in Excel returns BLANK'd measure when cumulating weekly totals, but not otherwise

[dharsanj]

Hi there,

I have a weird situation - maybe it's a DAX issue that is not supported in the Analyze in Excel mode. I have a report published on the service, where I am populating two tables:

Table 1 shows the weekly revenue by GEO (Attachment #1). Table 2 shows the cumulative revenue for the Quarter (Attachment #2). When I pull the same data using Analyze in Excel, the weekly revenue by Geo populates data (Attachment #3), but the Cumulative Revenue by Geo shows blanks (Attachment #4). 

There are 3 specific measures that drive the visual. Table 1 shows Measure 1 in the value field. Table 2 shows Measure 2 in the value field. The definitions of them are given below. I would greatly appreciate advise on why the Cumulative Revenue by Week is not showing up in the Excel pivot table, but does show up in the published PBI file. 

Thanks!

-------------------------------------------------------------------------------------------------------------

MEASURE 1: Weekly Revenue

A.com/Perpetual (Weekly) =
CALCULATE (

    SUM ( HanaTbl[ACTUAL_REVENUE_USD] ),

    HanaTbl[REVENUE_TYPE_DESC] = "Product",

    HanaTbl[REVENUE_MIX_DESC] = "Shrinkwrap",

    HanaTbl[SELLINCH_TEXT] = "Direct-B2C-Phone/Oth"

        || HanaTbl[SELLINCH_TEXT] = "Direct-B2C-Web",

    HanaTbl[PROFIT_CENTER_TRIMMED] <> 1749

)

MEASURE 2: Cumulative Revenue

A.com/Perpetual (cumulative) =
CALCULATE (

    [Cuml Revenue by Day],

    HanaTbl[REVENUE_TYPE_DESC] = "Product",

    HanaTbl[REVENUE_MIX_DESC] = "Shrinkwrap",

    HanaTbl[SELLINCH_TEXT] = "Direct-B2C-Phone/Oth"

        || HanaTbl[SELLINCH_TEXT] = "Direct-B2C-Web",

    HanaTbl[PROFIT_CENTER_TRIMMED] <> 1749,

)

MEASURE 3: Cumulative Revenue by Day (which is used in Measure 2 above)

Cuml Revenue by Day =
VAR FiscalQtr =

    SELECTEDVALUE ( DimFiscalQtr[FISCAL_YR_AND_QTR_DESC] )

VAR DayNum =

    SELECTEDVALUE ( HanaTbl[CALENDAR_DATE] )

VAR WkNum =

    SELECTEDVALUE ( HanaTbl[Wk Num Adj] )

RETURN

    IF (

        NOT ( ISBLANK ( DayNum ) ),

        CALCULATE (

            SUM ( HanaTbl[ACTUAL_REVENUE_USD] ),

            CALCULATETABLE (

                HanaTbl,

                HanaTbl[Wk Num Adj] <= WkNum,

                HanaTbl[CALENDAR_DATE] <= DayNum,

                HanaTbl[FISCAL_YR_AND_QTR_DESC] = FiscalQtr

            )

        ),

        CALCULATE (

            SUM ( HanaTbl[ACTUAL_REVENUE_USD] ),

            CALCULATETABLE (

                HanaTbl,

                HanaTbl[Wk Num Adj] <= WkNum,

                HanaTbl[FISCAL_YR_AND_QTR_DESC] = FiscalQtr

            )

        )

    )

Weekly Revenue from PBI ServiceCumulative Revenue from PBI ServiceWeekly revenue from "Analyze in Excel" PBI downloadCumulative Revenue from "Analyze in Excel" from PBI download

[v-jayw-msft]

Hi @dharsanj ,

I haven't heard of any limitations on the dax function for Analyze in Excel. It's weird that you have one visual that worked but one didn't. You may take a look at the document about Troubleshooting Analyze in Excel.

https://docs.microsoft.com/en-us/power-bi/collaborate-share/desktop-troubleshooting-analyze-in-excel 

Or you could check the Issues forum here:
https://community.powerbi.com/t5/Issues/idb-p/Issues.

Best Regards,

Jay