cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jaromir Frequent Visitor
Frequent Visitor

Memory issue - IF using calculated measure

Hi,

 

I am trying to calculate a day by day difference in stock values. To do that I have created a calculated measure called PrevStock:

 

PrevStock = CALCULATE(SUM('data'[stock]); PREVIOUSDAY('data'[date].[Date]))

 

Based on that measure I can calculate the difference between stocks in the consecutive days (measure):

 

Change = sum('data'[stock]) - 'data'[PrevStock]

 

Then I want to display data for a particular month, say February. The problem I am facing is that on 1st Feb "Change" shows a number which is equal to stock amount. I guess when I limit date range it cannot see any previous data so it thinks that "PrevStock" is 0.

 

I have tried to use IF statement:

Change = IF('data'[PrevStock] = 0; 0; sum('data'[stock]) - 'data'[PrevStock])

 

But it always runs out of memory. Why is that? How can I solve this? Thanks a lot!

 

Sample data:

2017-02-17 19_59_03.png

1 ACCEPTED SOLUTION

Accepted Solutions
CahabaData New Contributor
New Contributor

Re: Memory issue - IF using calculated measure

You might consider approaching it in the data model as follows:

* make a duplicate table T2

* in T2 make a calculated column that is (date less 1): PriorDate

 

Now join your Table to T2 using T1 date field and T2 PriorDate; or possibly even consider a merged table of the two - - kind of thinking out loud here....

 

* to avoid confusion in field names of T1 and T2 together you might change column name of T2 Stock to PriorStock

 

via Related method (or possibly the merged table) now you have Stock and Prior Stock in the same record and getting the difference is at your finger tips

www.CahabaData.com
2 REPLIES 2
Super User
Super User

Re: Memory issue - IF using calculated measure

Dax is not good at row by row comparisons. Take a look at my article here. 

http://exceleratorbi.com.au/use-power-query-compare-database-records/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
CahabaData New Contributor
New Contributor

Re: Memory issue - IF using calculated measure

You might consider approaching it in the data model as follows:

* make a duplicate table T2

* in T2 make a calculated column that is (date less 1): PriorDate

 

Now join your Table to T2 using T1 date field and T2 PriorDate; or possibly even consider a merged table of the two - - kind of thinking out loud here....

 

* to avoid confusion in field names of T1 and T2 together you might change column name of T2 Stock to PriorStock

 

via Related method (or possibly the merged table) now you have Stock and Prior Stock in the same record and getting the difference is at your finger tips

www.CahabaData.com

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 34 members 1,070 guests
Please welcome our newest community members: