DAX Row minus Previous Row

Archer · July 31, 2020, 4:00am

Hi Folks,

How do I get DAX to subtract row by previous row.
I am working on SQL Server direct query mode and unable to crack this for my report
Please advise and kindly refer to excel sample data and formula below I’ve tried

[TotalQty]  =
 Divide(SUM([delivered_quantity]),SUM([req_quantity]),0)`


PreviousRowSubtract = 
([TotalQty]) - 
CALCULATE (
            SUMX([delivered_quantity])/SUMX([req_quantity])*100,FILTER([Date]=dateadd([Date],-1,Day)))

Column D is what I am trying to achieve this would have other filter contexts like Year, MonthNum, WeekNum and Weekday in the form of Slicers

SampleData.xlsx (9.2 KB)

Many thanks
Archer

BrianJ · July 31, 2020, 6:44am

@Archer,

The key to this is adding an index column to your table. This makes it much easier to traverse up and down the column. After adding the index, create the following calculated column:

Previous Value = 

VAR PrevIndx =
CALCULATE(
    MAX( 'Table'[Index] ),
    FILTER(
        'Table',
        'Table'[Index] < EARLIER( 'Table'[Index] )
    )
)

VAR  Result =
CALCULATE(
    MAX( 'Table'[Total Qty] ),
    FILTER(
        'Table',
        'Table'[Index] = PrevIndx
    )
)

RETURN
Result

Once you’ve done that, it’s a simple matter to calculate the difference as

Difference = 
SELECTEDVALUE( 'Table'[Total Qty] ) - SELECTEDVALUE( 'Table'[Previous Value] )

Here’s what it looks like all put together:

I hope this is helpful. Full solution file attached below.

Brian
eDNA Forum - Previous Value Solution.pbix (19.1 KB)

P.S. EARLIER is the most poorly named function in the DAX catalog. I never fully understood how it worked until somebody told me to think of it as OUTER (in terms of row context), rather than “earlier”. Once I started thinking of it that way, the lightbulb just clicked on for me, and I now use it all the time.

Archer · July 31, 2020, 8:31am

Hey @BrianJ, As always much appreciate your wealth of insights/guidance towards issues posted here.
Both Index and Earlier are not working for me as I am on Direct Query mode so my question here is how can I handle this from DQ mode not Import Mode. Once again, can’t stress it enough that your solutions are spot on !
Thanks
Archer

BrianJ · July 31, 2020, 12:01pm

@Archer,

Ack! - my bad. Totally missed that when I read the question.

We can McGyver our way around this by building the index via RankX and using FILTER creatively to take the place of EARLIER. Will have something for you later this morning…

Brian

Archer · July 31, 2020, 1:57pm

Thanks @BrianJ you are of great help !

BrianJ · July 31, 2020, 2:34pm

@Archer,

OK, RankX-based index works like a champ.

DAX Index = 
RANKX(
    ALL( 'Table' ),
    [Date],,
    ASC,
    Dense
)

Retrieving the previous value just requires a couple of tweaks to our prior Previous Value measure - creation of a new variable to track our current row position, and referencing DAX Index instead of the PQ-created index we used before:

Previous Value2 =

VAR SelRow = 'Table'[DAX Index]

VAR PrevIndx =
CALCULATE(
    MAX( 'Table'[DAX Index] ),
    FILTER(
        'Table',
        [DAX Index] < SelRow
    ),
   ALL( 'Table'[DAX Index] )
)

VAR  Result =
CALCULATE(
    MAX( 'Table'[Total Qty] ),
    FILTER(
        'Table',
        'Table'[DAX Index] = PrevIndx
    )
)

RETURN
Result

And now this provides the same result as before, but should be compatible with DIrect Query limitations:

Hope this gets you what you need - shout back if it doesn’t. Full solution file posted below.

Brian
eDNA Forum - Previous Value Solution - Take 2.pbix (20.4 KB)

P.S. This construct is worth tucking in your back pocket, since it’s a good, flexible method for traversing columns that can be adapted for a wide variety of situations w/o the use of EARLIER. If you look in the SQLBI DAX guide, they recommend this approach even when EARLIER is available…

Archer · August 1, 2020, 1:56pm

Hi @BrianJ, This actually looks promising based on what I see however while implementing gives me an error “Measure, a single value for column in table cannot be determined” and I have tried to put it a Calc Column but doesn’t recognize DAX functions …
I tried the below with CountA and I’m getting 1 towards all dates… Could you please advise?

    DAX Index = 
RANKX(
    ALL( 'Table' ),
    COUNTA([Date]),,
    ASC,
    Dense
)

Index

BrianJ · August 1, 2020, 2:26pm

@Archer,

In my solution file, I created the DAX Index as a calculated column. So, you’re saying that in DQ it’s not letting you do that? If that’s the case, I think we’ll need to create a simple virtual table variable in the measure to provide the necessary evaluation context.

I’ll definitely review the DQ limitations- I only work in import mode in my own work, so I’m not immediately as aware of the constraints as I should be when I develop solutions.

Brian

Archer · August 1, 2020, 2:50pm

Yes please refer to the screenshot

BrianJ · August 1, 2020, 4:01pm

@Archer,

OK, new plan. I prefer to work with indexes over dates, but since Direct Query clearly doesn’t give a damn about my personal preferences, let’s play by its rules. So, instead of using index to traverse the column, we’ll use Table[Date]. Now the calculated column for Previous Value gets rewritten as:

Previous Value2 = 

VAR SelRow = 'Table'[Date]

VAR PrevDate =
CALCULATE(
    MAX( 'Table'[Date] ),
    FILTER(
        'Table',
        'Table'[Date] < SelRow
    ),
   ALL( 'Table'[Date] )
)

VAR  Result =
CALCULATE(
    MAX( 'Table'[Total Qty] ),
    FILTER(
        'Table',
        'Table'[Date] = PrevDate
    )
)

RETURN
Result

and Boom! We’re back in business.

Please let me know if this works OK. Take 3 solution file attached.

Brian

eDNA Forum - Previous Value Solution - Take 3.pbix (20.2 KB)

Archer · August 2, 2020, 7:06am

Hi @BrianJ, Unfortunately another road block
I couldnt get my around this error , would you know how to resolve this please?

Thanks
Archer

BrianJ · August 2, 2020, 7:32am

@Archer,

%$#@! - I really thought we had it this time…

Can you please send me your DAX code for this calculated column? I rechecked my solution and both the MAX function calls reference physical columns.

Previous Value2 = 

VAR SelRow = 'Table'[Date]

VAR PrevDate =
CALCULATE(
    MAX( 'Table'[Date] ),
    FILTER(
        'Table',
        'Table'[Date] < SelRow
    ),
   ALL( 'Table'[Date] )
)

VAR  Result =
CALCULATE(
    MAX( 'Table'[Total Qty] ),
    FILTER(
        'Table',
        'Table'[Date] = PrevDate
    )
)

RETURN
Result

Thanks.

Brian

Hemantsingh · August 2, 2020, 8:16am

Hi @Archer

There is no problem in the code as long as i can think of…Can you paste the exact code you are trying to create calculated column? May be there is some issue i can help with.

Regards

Archer · August 2, 2020, 8:49am

Hi
@BrianJ & @Hemantsingh,

Yes please find the below DAX Code

PrevValue =     VAR SelRow = orders[actual_finish_date]

    VAR PrevDate =
    CALCULATE(
    MAX( orders[actual_finish_date]) ,
    FILTER(
        orders,
        orders[actual_finish_date] < SelRow
    ),
       ALL( orders[actual_finish_date] )
    )

    VAR  Result =
    CALCULATE(
    MAX( SUM([del_quantity])/SUM([tot_quantity])*100 ),
    FILTER(
        orders,
        orders[actual_finish_date] = PrevDate
    )
    )

    RETURN
    Result

Hemantsingh · August 2, 2020, 9:08am

This is not allowed within MAX.
MAX( SUM([del_quantity])/SUM([tot_quantity])*100 ). Create another column in the orders table that Like this this
Percentcolumn = Divide([del_quantity],[tot_quantity],0)*100

Change MAX( SUM([del_quantity])/SUM([tot_quantity])*100 ) with
MAX( Orders[Percentcolumn] ).

Rest remains the same. I believe this should solve your max issue.

Regards,

Archer · August 2, 2020, 9:35am

Hey @Hemantsingh, I did what you proposed and now I get another error
I tried putting the same in a Measure and it accepts however I get no data

PrevValue

BrianJ · August 2, 2020, 10:22am

@Archer,

Try this - in the calculated column, instead of the second MAX expression, use MAXX( Orders, SUM([del_quantity])/SUM([tot_quantity])*100 )

Brian

Archer · August 2, 2020, 11:10am

Hi @BrianJ,
same error as previous !
I’ve put this in Calculated Column

PrevYieldValue = 

VAR SelRow = Orders[actual_finish_date]

VAR PrevDate =
CALCULATE(
    MAX( Orders[actual_finish_date]) ,
    FILTER(
        Orders,
        Orders[actual_finish_date] < SelRow
    ),
   ALL( Orders[actual_finish_date] )
)

VAR  Result =
CALCULATE(
    MAXX('Orders',SUM([del_quantity])/SUM([tot_quantity])*100 ),
    FILTER(
        Orders,
        Orders[actual_finish_date] = PrevDate
    )
)

RETURN
Result

I get an error as below
PrevValue

I have tried Measure and I get blank(No values)

PrevValue = 

VAR PrevDate =
CALCULATE(
    MAX( Orders[actual_finish_date]) ,
    FILTER(
        Orders,
        Orders[actual_finish_date] < Orders[actual_finish_date]

    ),
   ALL( Orders[actual_finish_date] )
)

VAR  Result =
CALCULATE(
    MAXX('Orders',SUM([delivered_quantity])/SUM([total_quantity])*100 ),
    FILTER(
        Orders,
        Orders[actual_finish_date] = PrevDate
    )
)

RETURN
Result

MeasurePrevValue

Thanks
Archer

BrianJ · August 2, 2020, 2:22pm

@Archer,

Wow, I am starting to intensely dislike Direct Query mode…

At this point, I really need a sample PBIX file to work from. If you have sensitive information in your dataset, here’s a video I put together with quick, simple strategies for how to mask it:

I have some ideas about how we can generate the result using LOOKUPVALUE without using CALCULATE, but I want to test it out on actual/representative data from your model.

Thanks.

Brian

Archer · August 2, 2020, 3:20pm

Hey @BrianJ,

Can you please confirm if I need to convert my PBIX from DQ to Import and Share it here ? Yes I am aware of masking the data which I previously learned from this very same video you tagged here

Thanks
Archer