Calculate Initial and final sales order status based on dates filter for each sales order id

[Mohan128256]

Hello All,

I am trying to calculate the sales order status from the point of a product was ordered.

the data which i have for one of the sales order id is as below for a customer.

Sample Data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdK9DkBAEATgV5GrXTJ36+doUahoVKJiC4kgV3h+p1aQ7GaameTrdhyVAUJ6PEe5ilXqXEKZboYqFOM0SFtYioASCFN38h51fmGvpvjTFzJvIfRG6O3L17ytF3te/nCS8UTGUxnPZDyX8ffntft1rPOjpxs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order ID (H)" = _t, #"Product ID (L)" = _t, SnapDate = _t, #"Sales Order Status Name (H)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Order ID (H)", type text}, {"Product ID (L)", type text}, {"SnapDate", type text}, {"Sales Order Status Name (H)", type text}})
in
    #"Changed Type"

I am trying to calculate the initial sales order status and final sales order status in a measure based on the dates filters.

For this perticular sales order id, the dates are from 18-03-2023 to 28-03-2023 and the initial status is Open Order so the measure should return Open Orderv and Invoiced as final status in all rows.

But if the date range selected from 21-03-2023 to 24-03-2023 then the measures should return as below

if the date range selected from 23-03-2023 to 28-03-2023 then the measures should return as below

Can anyone please help me with the dax.

Thanks,

Mohan V.

[amitchandak]

@Mohan128256 , I think this latest code should work for last status in selected ranges. (using allselected)

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

You can use min to get first status in selected range

[Mohan128256]

@amitchandak  I have modified my dax after couple of tries and i got the solution which is giving the right result.

Ordered Qty (L) Before = 
VAR _SalesOrderID = MAX('Sales Orders Snapshots'[Sales Order ID (H)])
VAR _ProductID = MAX('Sales Orders Snapshots'[Product ID (L)])
var _max = MINX(filter(ALLSELECTED('Sales Orders Snapshots'), 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID), 'Sales Orders Snapshots'[SnapDate]) 
return 
CALCULATE(MAX('Sales Orders Snapshots'[Ordered Qty (L)]), filter(ALLSELECTED('Sales Orders Snapshots') ,  'Sales Orders Snapshots'[SnapDate] =_max && 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID))  
 

But now i am facing another issue with the performance of this query.

When i do select individual sales order id or customer name from slicer the query is running fine and showing the results.

But if i dont select anythig from any of the slicer, it keeps spinnings and after waiting for almost 15 to 20min it gives an error as Resuourses exceeded try filtering to decrease the amount of data displayed.

The data which i am having in my report of this table is around 9,64,000.

Could you please help me to optimize this dax query.

Thanks,

Mohan V.

[Mohan128256]

@amitchandak thanks for the reply.

I have tried the below dax for initial and final status.

Sales Order Status Name Initial = 
var _max = MINX(filter(ALLSELECTED('Sales Orders Snapshots'), 'Sales Orders Snapshots'[Sales Order ID (H)] = Max('Sales Orders Snapshots'[Sales Order ID (H)])), 'Sales Orders Snapshots'[SnapDate]) 
return 
CALCULATE(max('Sales Orders Snapshots'[Sales Order Status Name (H)]), filter(('Sales Orders Snapshots') ,  'Sales Orders Snapshots'[SnapDate] =_max))

 But getting the output as below.

But i would like to have it in all of the rows of the data as below image.

Please help.