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

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

Reply
DPE_Fabulous
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":

Substract.png

Thank for your help and take care.

 

Fabien

 
 

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @DPE_Fabulous 

 

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])

vzhangti_0-1658470912948.png

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.

View solution in original post

7 REPLIES 7
v-zhangti
Community Support
Community Support

Hi, @DPE_Fabulous 

 

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])

vzhangti_0-1658470912948.png

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.

tamerj1
Super User
Super User

Hi @DPE_Fabulous 

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

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

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.

hashtag_pete
Helper V
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

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 !

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)

 

 -1row.png

Does that help? If so, please

 

  • mark this post as a solution
  • give kudos

Thanks hashtag_pete

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.