Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dirkkoch
Helper III
Helper III

Dynamically subtract two values in same column between two dates

Hi,

 

I have two columns (one with values, one with dates, see Screenshot marked red). I want to create a calculated column which subtracts each value with the one from the former date (see desired result written in the column on the right hand side). 

I know I have to use the earlier-function but I did not get what I wanted so far.

 

I would be thankful for support!

 

dirkkoch_0-1669108732836.png

 

1 ACCEPTED SOLUTION
MahyarTF
Memorable Member
Memorable Member

Hi,

I did as below :

1- Add the Index column to the table in Power Query.

2- Then use below code for create the new column :

DiffVal Col =
Var __CurrIndex = Sheet57[Index]
Var __CurVal = (Sheet57[Value])
Var __BeforeVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __CurrIndex - 1 ))
return __CurVal - __BeforeVal
MahyarTF_0-1669111035938.png

Appreciate your Kudos and please mark it as solution if it helps you

Mahyartf

View solution in original post

8 REPLIES 8
dirkkoch
Helper III
Helper III

@daXtreme Ok, thanks. And how to do it in Power Query?

MahyarTF
Memorable Member
Memorable Member

Hi,

I did as below :

1- Add the Index column to the table in Power Query.

2- Then use below code for create the new column :

DiffVal Col =
Var __CurrIndex = Sheet57[Index]
Var __CurVal = (Sheet57[Value])
Var __BeforeVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __CurrIndex - 1 ))
return __CurVal - __BeforeVal
MahyarTF_0-1669111035938.png

Appreciate your Kudos and please mark it as solution if it helps you

Mahyartf

@MahyarTF 
Thanks! Yes, that would work if there is a value in each row of the column. Unfortunately there might be some blanks, meaning that if there is no value, it should substract the actual with the last availabe value in the column 

dirkkoch_0-1669114374305.png

 

Hi @dirkkoch 

Please try the below code in the new column I described earlier :

DiffVal Col =
Var __CurrIndex = Sheet57[Index]
Var __PreviousIndex = CALCULATE(max(Sheet57[Index]), all(Sheet57), Sheet57[Index] < __CurrIndex, not(ISBLANK(Sheet57[Value])))
Var __BeforePreviousIndex = CALCULATE(max(Sheet57[Index]), all(Sheet57), Sheet57[Index] < __PreviousIndex, not(ISBLANK(Sheet57[Value])))
Var __PreviousVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __PreviousIndex))
Var __BeforePreviousVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __BeforePreviousIndex))
Var __NextVal = CALCULATE(sum(Sheet57[Value]), filter(sheet57, Sheet57[Index] = __CurrIndex+1))
Var __CurVal = if(ISBLANK(Sheet57[Value]), __NextVal,Sheet57[Value])

return if(ISBLANK(Sheet57[Value]), __PreviousVal - __BeforePreviousVal, __CurVal - __PreviousVal)
MahyarTF_0-1669167935012.png

Appreciate your Kudos and please mark it as a solution if it helps you

Mahyartf

@MahyarTF  Thanks for your support! I managed to do it myself now by creating an adapted Index (with Rankx-Function) leaving out the blank spots. That worked for me!

@MahyarTF 

 

THe problem with your solution is that it makes the model unnecessarily bloated (as the index column is not really needed). Second, calculated columns are not compressed the way they are when created in Power Query - this results in even more bloat. In big models this may be unacceptable. Therefore, please use Power Query to do such calculations. Unless, of course, you don't care about how big the model is and how fast DAX could be. But I think you should since you pay money for storage. You also want to have fast DAX and columns optimized for access and calculations.

Hi @daXtreme ,

Thanks for your useful advice.

Just learning and trying to improve my knowledge.

This is the solution it was at top of my head but absolutely there are better solutions.

Mahyartf
daXtreme
Solution Sage
Solution Sage

Use Power Query for this because this is where such calculations belong. By the way, the EARLIER function in DAX has been discouraged and you should learn to use variables instead.

daXtreme_0-1669110771309.png

Source: https://dax.guide/earlier 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.