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.
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
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.
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”…
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.
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 !
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!
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
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)
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…):
Create a virtual table variable that filters the data based on the IsHoliday and IsWorkingDay columns in the Extended Date Table
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.
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.
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).
I hope this is helpful. Full solution file attached below.
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…