- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Measure to calculate current value minus previous ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

agnes_

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
02:28 AM

Hey!

I have the same problem for some time now that I really can't solve. I am doing a calculation to calculate the total energy,

I have following as example:

I have a column with an accumulative TotalEnergy for every device, I have the devices as dirfferent name, Index and I made a Rank with following formula (Where EndUtc is Date and time)

Rank = RANKX(FILTER(Data, Data[Device] = EARLIER(Data[Device])), Data[EndUtc],,ASC)

I wanna make a calculation NEWENERGYPERDAY: as for device A, 365-364 = 1, 364-364=0 etc, for every row.

I have tried many different calculations, as for example:

NewEnergyPer300s_kWh =

VAR minIndx = CALCULATE (

MAX ( Data[Index] ),

ALLEXCEPT ( Data, Data[Device] ),

Data[Index] < EARLIER (Data[Index] )

)

RETURN

IF (

ISBLANK ( minIndx ),

BLANK (),

Data[TotalEnergy_kWh]

- CALCULATE (

SUM ( Data[TotalEnergy_kWh] ),

Data[Index] = minIndx,

ALL ( Data )

)

)

which worked fine, except that I am getting not enough memory capacity, which is weird cause I don't even have too much data, and when I find other formulas and using function EARLIER I get the same issue evrytime, is it possible to write this formula or similar as a measure and not a column?

And also if there are any better way to write the calculation that will be better for the memory capcity?

Thanks! Feeling a bit lost with this issue.

Kind regards

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Kristjan76

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
08:24 AM

Hi again,

Can you share your file? I am sorry but I think that I have overlooked some important facts in original post, but if you have created a Index column that increases with every new entry per device you should be able to use this calculated column. And perhaps you do not have to use the device varible at all (i.e. if i Index contains unique values)

EnergyPerDay = VAR device = Data[Device] // could be skipped VAR ind = Data[Index] VAR preInd= ind-1 VAR energyPreDate = SUMX( FILTER( Data, Data[Index] = preInd

&& Data[Device] = device // could also be skipped ), Data[TotalEnergy_kWh] ) RETURN Data[TotalEnergy_kWh] - energyPreDate

Best regars,

Kristjan

v-cherch-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
09:37 PM

Hi @agnes_

You may try to create below measures:

NextValue = CALCULATE ( SUM ( Data[TotalEnergy_kWh] ), FILTER ( ALLEXCEPT ( Data, Data[Device] ), Data[Rank] = MAX ( Data[Rank] ) - 1 ) )

NEWENERGYPERDAY = IF ( ISBLANK ( [NextValue] ), BLANK (), SUM ( Data[TotalEnergy_kWh] ) - [NextValue] )

Regards,

Cherie

Community Support Team _ Cherie Chen

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

If this post

12 REPLIES 12

Kristjan76

Member

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
03:20 AM

Hi agnes_,

You can try this,

EnergyPerDay = VAR device = Data[Device] VAR d = Data[Date] VAR tbl = FILTER( Data, Data[Date] < d && Data[Device] = device ) VAR preDate = MAXX( tbl, Data[Date]) VAR energyPreDate = SUMX( FILTER( tbl, Data[Date] = preDate ), Data[TotalEnergy_kWh] ) RETURN Data[TotalEnergy_kWh] - energyPreDate

If this works please LIKE and mark as solutions.

P.s. if there is no gap in the dates, you can change VAR preDate = MAXX( tbl, Data[Date]) to preDate = d-1 and then use preDate in the tbl varible right away.

EnergyPerDay = VAR device = Data[Device] VAR d = Data[Date] VAR preDate = d-1 VAR tbl = FILTER( Data, Data[Date] = preDate && Data[Device] = device ) VAR energyPreDate = SUMX( tbl, Data[TotalEnergy_kWh] ) RETURN Data[TotalEnergy_kWh] - energyPreDate

Regards,

Kristjan

agnes_

Regular Visitor

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
04:16 AM

Thanks!

But everytime I try this as a measure I get the same problem, that PowerBI can't find the column tables, and the names are correct: It find it at some places and some not.

If I put max, it can find it though:

agnes_

Regular Visitor

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
04:33 AM

And as a new column, same here not enough memory to complete the operation, which is weird cause I have other files that have more data then this one.

Kristjan76

Member

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
05:03 AM

Hi again,

If Data[EndUtc] only contains one row for each device and there are no missing dates you should try the second version of the calculated column. How many rows do you have in your table?

Regards,

kristjan

LAPORTES

Regular Visitor

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
05:06 AM

Hello my question won't help but im curious : the same formula works on a bigger table than the one with memory error ? thank you

Kristjan76

Member

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
05:33 AM

LAPORTES

Regular Visitor

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
05:37 AM

My question wasn't for you

Agnes said that she got an memory error with the formula. But she had bigger tables. I wanres to be sure that she did run the actual formula on the big table as well.

If not clear, forget it, not a big deal !

Agnes said that she got an memory error with the formula. But she had bigger tables. I wanres to be sure that she did run the actual formula on the big table as well.

If not clear, forget it, not a big deal !

agnes_

Regular Visitor

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
05:44 AM

[EndUtc] have have data every 300s (every 5th minute of the day) but only one EndUtc for every device, so if there are five devices, there are five 2019-07-30 13:25:00 stamps for example.

I have 68,152 rows, 30 Columns, The memory capacity error occurs with Earlier function.

Another PowerBI I have been working with the same type of calculation

NewEnergyPerDay_kWh = var maxIndex =CALCULATE( MIN('Data'[MinIndex]), FILTER('Data','Data'[MinIndex]=EARLIER('Data'[MinIndex]))) VAR minVal='Data'[TotalEnergy_kWh] var maxVal= IF('Data'[Index]=maxIndex, LOOKUPVALUE('Data'[TotalEnergy_kWh],'Data'[Index],'Data'[Index]), LOOKUPVALUE('Data'[TotalEnergy_kWh],'Data'[Index],'Data'[Index]+1)) RETURN CALCULATE(DIVIDE(minVal-maxVal,1,0),FILTER('Data','Data'[MinIndex]=EARLIER('Data'[MinIndex])), ALLEXCEPT(Data,Data[Device]))

where:

MaxIndex = CALCULATE(MAX('Data'[Index]),ALLEXCEPT('Data','Data'[Device])) MinIndex = CALCULATE(MIN('Data'[Index]),ALLEXCEPT('Data','Data'[Device]))

there I don't get any memory error, but still some error:

agnes_

Regular Visitor

Re: Measure to calculate current value minus previous row for a specific device

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019
05:51 AM

Yes, second formula worked without getting the memory capacity, but the results gets very high negative values, and the values should not be high at all, and positive, maybee I should use some LOOKUPVALUE, instead of the SUMX one?