Some measures are breaking after 2nd incremental refresh

Question

Hi everyone,&nbsp;This is my first time asking a question here and I'm a newbie so please bare with me.&nbsp; &nbsp;&nbsp;I have a report hosted in Power BI service and have setup incremental refresh.&nbsp; The incremental refresh seems to work fine, all of my data is being refreshed and updated with new data.&nbsp; The problem is that certain measures are calculating incorrectly, not after the first time I run incremental refresh, but after the 2nd time.&nbsp; The measures calculate percent change for daily stock price data over varying time frames.&nbsp; So I have measures calculating percent change for 1 day, 1 wk, 1 month, 3 months, 6 months and 12 months.&nbsp; What's weird is that the 12 months measure continues to calculate correctly while some of the others just return -100%.&nbsp; Also, the measure continues to calculate correctly for some stocks, but not others.&nbsp; The measures calculate correctly when I refresh in Power BI Desktop.&nbsp; All the measures are the exact same the only difference being the number I'm subtracting from the Index to get the previous price for the previousPrice variable.&nbsp; Here is the measure I'm using. . .&nbsp;&nbsp;ETFdaily%Return = VAR LastPrice =&nbsp; &nbsp; &nbsp;CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( etfprices[close] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER ( etfprices, etfprices[date] = MAX ( etfprices[date]) ))VAR previousPrice =&nbsp; &nbsp; &nbsp;CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES ( etfprices[close] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER ( etfprices, etfprices[Index] = MAX ( etfprices[Index] )-1 ))RETURN&nbsp; &nbsp; &nbsp;DIVIDE(LastPrice, previousPrice)-1&nbsp;Below is a screenshot of the report after refreshing twice in Power BI service.&nbsp; I am using cards to visualize the measure filtered by the individual stock that I want display.&nbsp;&nbsp;&nbsp;Any help is greatly appreciated and thanks in advance!!!&nbsp;

lbendlin · Answer

Here's what you need to remember about incremental refresh.&nbsp; Every time you make a structural/meta data change to your report (doesn't even have to be a change for the data source that you have defined the incremental refresh for) the Power BI service will do a full refresh, and will create all the partitions according to your "keep" specifications.
Only starting with the second refresh will it actually run an incremental refresh, touching only the partitions that you have specified in the "update"&nbsp; timeframe.
&nbsp;
You need to provide more details about your incremental refresh setup (especially if you chose to enable data change checks),&nbsp; but maybe this explanation will already give you the reason why you see that behaviour.

lbendlin · Answer

The values of the RangeStart and RangeEnd parameters in Power Query are not relevant for the incremental refresh (they are only useful to limit the amount of data the developer has to work with in Power BI Desktop.
&nbsp;
What _is_ important is that these two parameters are used as filters in your respective Power Query code. One of them has to be inclusive, the other exclusive.
&nbsp;
Maybe you can verify that your Power Query code satisfies these requeirements.

nalyons1 · Answer

Hi lbendlin,&nbsp;I really appreciate you taking the time to help me.&nbsp; You're first response definitley cleared up a few things for me.&nbsp; I went ahead and tried deselecting the "enable data change checks", re-published and initiated incremental refresh - and got more or less the same results.&nbsp; I'm not exactly sure what you mean by "Every time you make a structural/meta data change to your report (doesn't even have to be a change for the data source that you have defined the incremental refresh for) the Power BI service will do a full refresh, and will create all the partitions according to your "keep" specifications."&nbsp;&nbsp;Could you clarify a bit more?&nbsp; The etfprices, rvol30 and impliedVol tables are updated daily and all have the same incremental refresh configuration as below.&nbsp;&nbsp;Below are screenshots of my incremental refresh configuration.&nbsp; Let me know if there is something off in there, where else I might be going wrong, or if there is anything else I could provide to make it easier for you to understand the issue.&nbsp; &nbsp;&nbsp;Thanks so much once again for your help!!!&nbsp;My parameters:RangeStart ParameterRangeEnd Parameter&nbsp;My custom filter on the etfprices table "date" column:&nbsp;My incremental refresh configuration for the etfprices table:

nalyons1 · Answer

I see.&nbsp; I believe that the Power Query does satisfy those requirements.&nbsp; Below is a screenshot of the M code for the custom filter on the "date" column of the etfprices table.&nbsp; Is this what you're referring to?&nbsp;&nbsp;&nbsp;

lbendlin · Answer

yes but you want to move that filter up as much as possible. No point in doing transformations to rows that you then throw away.
&nbsp;
Are you familiar with partitions and how to check them?

Forum Discussion

Some measures are breaking after 2nd incremental refresh

7 Replies

Related content

Increment Refresh

Incremental refresh

Measure used as a Filter breaks another measure

Measure breaks visual

Incremental Refresh