DAX Row minus Previous Row

@Archer,

To make it as easy as possible for you, I think all I really need at this point is just a masked representative subset of your Orders table. I’ll link that with the extended date table to figure out how we can retrieve the result value from that table, without using a CALCULATE statement or any of the other restrictions under Direct Query.

Sending that table to me in Excel or CSV would be fine.

Thanks.

  • Brian

Hey @BrianJ,

Please find the G-Drive link for the data from Orders

https://drive.google.com/file/d/1ebr_3BGtcav5UaC9owdexTcH7LBfu-CK/view?usp=sharing

Thanks
Archer

@Archer,

Okay, stop me if you’ve heard this one before, but I think I’ve got the measure working in a way that doesn’t violate any Direct Query DAX limitations. Having your actual data to work with was a big help here:

Prev Val2 = 

VAR SelDate = SELECTEDVALUE( Dates[Date] )

VAR PrevDate =
CALCULATE(
    MAX( Dates[Date] ),
    FILTER(
       ALL(Dates[Date]),
       Dates[Date] < SelDate
    )
)

VAR PrevDeliv =
CALCULATE(
    SUM( Orders[delivered_quantity] ),
    FILTER(
        ALL(Dates),
        Dates[Date] = PrevDate
    )
)

VAR PrevAggQty =
CALCULATE(
    SUM( Orders[total_quantity] ),
    FILTER(
        ALL(Dates),
        Dates[Date] = PrevDate
    )
)

VAR Result =
DIVIDE(
    PrevDeliv,
    PrevAggQty
)

RETURN
Result 

image

Please let me know if this works for you.

Thanks. Full solution file attached below.

Hey @BrianJ,
Thanks for this great solution which I believe we’re closer however it doesn’t work as desired

When I select only one date from Weekday slicer it works correctly
PrevValue2Measure

When I select all from Weekday slicer it doesnt show any value
PrevValue2Measure1

The intent is to have the Prev Val2 displayed for all selections so that calculation happens dynamically and I can create an IMR Chart (Line Chart)

Thanks
Archer

I have also tried this in Measure but no luck , I get the same behaviour only when value is selected from date it gives me Prev Val else blank

Prev Val2 = 

VAR SelDate = SELECTEDVALUE( Dates[Date] )

VAR PrevDate =
CALCULATE(
    MAXX(Dates, Dates[Date] ),
    FILTER(
       ALL(Dates[Date]),
       Dates[Date] < SelDate
    )
)

VAR PrevDeliv =
CALCULATE(
    SUMX(Orders, Orders[delivered_quantity] ),
    FILTER(
        ALL(Dates),
        Dates[Date] = PrevDate
    )
)

VAR PrevAggQty =
CALCULATE(
    SUMX(Orders, Orders[total_quantity] ),
    FILTER(
        ALL(Dates),
        Dates[Date] = PrevDate
    )
)

VAR Result =
DIVIDE(
    PrevDeliv,
    PrevAggQty
)*100

RETURN
Result 

I attempted to try this in Calc Column and all I get is limitation of DQ same old error

Please advise

Thanks
Archer

@Archer,

Okay, the fact that we’re not getting any DQ errors means we’re over the hump here. Now that I know what you want the final visual to look like, it shouldn’t be difficult to adjust the measures accordingly. I’ll work on it this evening after work, and have a solution back to you tonight.

  • Brian

P.S. - I did a lot of reading last night on Direct Query, and calculated columns are a dead end here. The only calculated columns that will work are really simple ones that don’t require CALCULATE. I found the attached white paper by Alberto Ferrari, which was extremely helpful in understanding the full range of DAX limitations in DQ mode.

DirectQuery in Analysis Services - Whitepaper.pdf (2.1 MB)

2 Likes

Thanks a ton @BrainJ, I have been looking for this sort of documentation for a while now(~3 months)

@BrianJ

Thanks for sharing the whitepaper because DQ was driving me :crazy_face:

@Melissa,

This is my first real exposure to working within the DQ limits and honestly, I’m amazed anyone sticks with PBI under those limitations. It’s like trying to write a coherent speech if the only vowels you could use were “i” and “y”…

  • Brian
2 Likes

@Archer,

Okay, this is strange. I added all three slicers from your most recent post to the solution I sent you last night (Take 4) and they seem to work fine, regardless of whether selections are made in all three slicers, no slicers or any combination in between.

I’m wondering how you have your data model set up? - mine has an active 1:m one-way relationship between Dates[Date] and Orders[Date]. Is yours set up the same way? If so, I’m at a loss to explain why my solution works and yours doesn’t. At that point, I think you’ll need to provide me your full PBIX file so that I can dissect it and figure out what’s going sideways with the prior value calculation interacting with the slicers.

In the interim, I’ve attached my full solution file (which is just last night’s file with the three slicers added).

I hope this is helpful – I think were getting really close to finally slaying this beast.

  • Brian

Snag_16a64aab

Snag_16a689d7

https://drive.google.com/file/d/1jeZ5QFROpyo3wS-t7uYXODmHMJG6ZzR6/view?usp=sharing

2 Likes

Amazing efforts in this thread to get to a solution

Hey @BrianJ, you cracked this like a guru ! amazing solution and it was my mistake I changed data model a bit to make things work and when resumed to original state your solution worked like a charm. This works like a breeze in DQ now and I am almost done with my IMR Chart plotting with deriving AVG, UCL and LCL. Phew!! what a ride it was and I am so thankful to you to say the least. Much appreciated and you are phenomenal at lending help my friend !

@Archer,

Hey, great to hear that’s finally working well for you. When I first read your question, I figured I’d crack this one on the first try. 30 posts later…

I ended up learning a ton about Direct Query DAX limitations, and really enjoyed working through this with you.

Thanks, and good luck with the rest of your project!

  • Brian
1 Like

Hey @BrianJ, It is indeed and you did crack it on first try with import so it kinda counts too

I would need some direction on Additional logic on retrieving previous value. Requirement is to exclude Weekends and bring Friday Value over to Monday as Prev Value so I used the below DAX

Prev Val2 = 
VAR SelDate = SELECTEDVALUE( Dates[Date] )
VAR Weekend = SELECTEDVALUE(Dates[IsWeekend]) = "True"
VAR Workday = SELECTEDVALUE(Dates[WeekDayName]) = "Monday"

VAR PrevDate = IF(Weekend = TRUE(),Blank(),
If(Workday = FALSE(),
CALCULATE(
    MAX( Dates[Date]  ),
    FILTER(
       ALL(Dates[Date] ),
       Dates[Date]  < SelDate 
    )),
    CALCULATE(
    MAX( Dates[Date]  ),
    FILTER(
       ALL(Dates[Date] ),
       Dates[Date]  < SelDate - 2 ))))

How can I achieve Holiday’s as well ? I have IsHoliday Flag coming from my Dates time dimension table so whenever there is a holiday it is giving blank prev value wherein I would need to retrieve the previous working day value

Please find the sample data for your reference
data.xlsx (12.1 KB)

Thanks
Archer

@Archer,

Sorry for the delayed response on this - I had my head down yesterday getting my Data Challenge #5 entry completed and posted.

I’ll take a detailed look at this today and hopefully get back to you by the end of the day with a full solution. My initial take is that there might be a very simple solution on this (record scratch…15 posts later…):

  1. Create a virtual table variable that filters the data based on the IsHoliday and IsWorkingDay columns in the Extended Date Table
  2. Run our existing measure code on that virtual table

Will get back to you soon.

  • Brian

P.S. @EnterpriseDNA - can you please break this post and @Archer’s previous post into a new separate thread entitled “Obtaining Previous Value Excluding Weekends and Holidays”. Thanks.

Thanks for your Response @BrianJ

So I believe in our scenario, if we can mark Weekends & Holidays as IsWorkingDay = False then it will get us the desired result?

@Archer,

This one ended up being pretty straightforward. We just needed to take our previous measure and add the following filter conditions in the PrevDate variable.

image

If we look at 5/23 - 5/26 below, we can see that this is working as expected. Monday picks up the Friday value, but in this case so does Tuesday, since Monday is a holiday (Memorial Day - I’m assuming US holidays, but if you plug in your holiday table column parameter into the extended date table, it will work regardless of where you are, as long as you set up your holiday table correctly).

image

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

– Brian
https://drive.google.com/file/d/1BzkFs6iYK0CSyLrFCauSxNLybII9T6bw/view?usp=sharing

@BrianJ, Awesome you cracked this at one shot this time ! Thanks a ton and much appreciated

@Archer,

Great – glad that got you what you needed. Can’t take too much credit for this one - @Melissa’s extended date table did most of the heavy lifting here…

  • Brian