cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## How to create a new measure who calculate the difference between the previous row and the actual?

Hi everybody,

I hope this message finds you well across the world !!

Since last Friday, I'm trying to do a measure in DAX who make a substraction (or addition) like: [Row N] - [Row N-1].

It's very usefull this kind of measure to  see the profit or the difference of sales through dates.

I attached a screenshot where you can find the "logic":

Thank for your help and take care.

Fabien

1 ACCEPTED SOLUTION
Community Support

You can try the following methods.

PreValue =
Var PrevDate=MAXX(FILTER(ALL('Table'[Date]),'Table'[Date]<SELECTEDVALUE('Table'[Date])),'Table'[Date])
Var PreValue=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Date]=PrevDate))
Return
PreValue
Measure = IF([PreValue]<>Blank(),SUM('Table'[Value])-[PreValue])

Is this the result you expect?

Solved: Calculate difference of data from previous availab... - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

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

7 REPLIES 7
Community Support

You can try the following methods.

PreValue =
Var PrevDate=MAXX(FILTER(ALL('Table'[Date]),'Table'[Date]<SELECTEDVALUE('Table'[Date])),'Table'[Date])
Var PreValue=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Date]=PrevDate))
Return
PreValue
Measure = IF([PreValue]<>Blank(),SUM('Table'[Value])-[PreValue])

Is this the result you expect?

Solved: Calculate difference of data from previous availab... - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

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

Super User

it really depends on your data model. In general you can retrieve the current date using

MAX or MIN ( TableName[Date] )

store it in a variable (CurrentDate)

then calculate the previous value like

VAR PreviousValue =

CALCULATE ( SUM ( TableName[Column] ), TableName[Date] = CurrentDate )

the CurrentValue would simply be

SUM ( TableName[Column] )

the result would be

CurrentValue - PreviousValue

Frequent Visitor

Hello,

Thank you for the answer. I would like to have this table in a tooltip viz.

The problem I see with your function is that we take the MIN/MAX and we can't calculated the other date's values.

If we store the MAX(DATE), it's going to be the 18.07.2022 and the previous 17.07.2022. But I would like to have the others too...

I hope you can understand my problems with this table !

Thank you.

Fabien

Super User

Hi @DPE_Fabulous
The MAX will retrieve the maximum date in the current filter context which is in fact one value. Therefore, whether you use MAX, MIN, SELECTEDVALUE, AVERAGE, SUM or even VALUES you will get the same result which the date value in the current filter context. The only difference will be at the subtotal/grand total level where each of these functions will result in a different outcome (for example SELECTEDVALUE will be blank and VALUES will result in an error and the result of the other 4 is clear). I'm just worried about your data model and what would be the resulted shape of the filter context.

Helper V

Hello Fabien,

you can try PARALLELPERIOD and then take your first measure minus PARALLELPERIOD.

The function is described here.

PARALLELPERIOD function (DAX) - DAX | Microsoft Docs

The idea is not to take row-1, but to use day-1, or quarter, or whatever granulatity you want to use.

Kind regards

hashtag_pete

Frequent Visitor

Hello,

Thank you for your answer. Nevertheless, the PARALLELPERIOD can't accept the granularity "day" as <interval> (said in the link you gave me). I didn't knew this function so thank you !

Helper V

Hello @DPE_Fabulous

😅true, I missed that, sorry!

Another idea would be to use SELECTEDVALUE and CALCULATE. I have tried this and it seems to work (unless I have misunderstood your problem):

Day Diff =
var SelectedDay =
SELECTEDVALUE('Date'[Date]) -1
return

[Total Sales] -
CALCULATE(
[Total Sales],
'Date'[Date] = SelectedDay)

Does that help? If so, please

• mark this post as a solution
• give kudos

Thanks hashtag_pete

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors