DAX Row minus Previous Row

Hemantsingh · August 2, 2020, 8:16am

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

BrianJ · August 2, 2020, 6:08pm

@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

Archer · August 3, 2020, 6:12am

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

BrianJ · August 3, 2020, 9:43am

@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

Please let me know if this works for you.

Thanks. Full solution file attached below.

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

Archer · August 3, 2020, 10:27am

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

Archer · August 3, 2020, 12:51pm

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

BrianJ · August 3, 2020, 1:20pm

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

Archer · August 3, 2020, 1:45pm

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

Melissa · August 3, 2020, 1:53pm

@BrianJ

Thanks for sharing the whitepaper because DQ was driving me

BrianJ · August 3, 2020, 1:59pm

@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

BrianJ · August 3, 2020, 11:19pm

@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

sam.mckay · August 3, 2020, 11:22pm

Amazing efforts in this thread to get to a solution

Archer · August 4, 2020, 5:12am

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 !