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

[DPE_Fabulous]

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

[v-zhangti · Accepted solution]

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

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]

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

[DPE_Fabulous]

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

[tamerj1]

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]

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

[DPE_Fabulous]

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 !

[hashtag_pete]

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