Forum Discussion

marcorusso's avatar
marcorusso
Icon for Most Valuable Professional rankMost Valuable Professional
3 years ago

So you want this?

Ordered Qty (L) Before = 
VAR _FilterSnap =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Sales Orders Snapshots',
                'Sales Orders Snapshots'[Sales Order ID (H)],
                'Sales Orders Snapshots'[Product ID (L)]
            ),
            "@SnapDate", MIN ( 'Sales Orders Snapshots'[SnapDate] )
        ),
        ALLEXCEPT (
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Sales Orders Snapshots'[Ordered Qty (L)] ),
        ALLEXCEPT (
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        ),
        KEEPFILTERS ( _FilterSnap )
    )
Mohan128256's avatar
Mohan128256
Icon for Helper IV rankHelper IV
3 years ago

marcorusso I really appreciate your efforts here but unfortunately these queries are not giving the right result which i am expecting as mentioned in my above question.

Let me take you the ask here once again.

Sample Data which you can check here:

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"

or 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdtPSwJRFEDxryKuHbjz5m9upSCIEGsnLqKCIigw+v5NurCmM4s2nReTF9/Gg3J/u7fdzqMuoipSpGoW7TLKZdnNF/P1x/7+6e79cfa2f3jcDweXq+J2c31zcb4Z3pQRw7OOw189HFw9v74cz4fX4Wn6LkUUq+rrsD99QCwPz3B6+t8ttIiyS/1oiDVV/BpmTR39aIg1TZTtaIg1XRy+lO9DrOmbth8Nc6dSC1MMquP4M/2c/xN0lgN9GKHRhzUafVxj0Yc1Gn1Yo9GHNRp9vFMifRgk0pciA/o4wqKPayz6Jmok+rjGoo9rLPq4xqJvYqc8+jjIpK/MgT6M0OjDGo0+rrHowxqNPqzR6MMajT7eKZE+DDLpSznQhxEafVij0cc1Fn1Yo9GHNRp9WKPRxzsl0odBJn1VDvRhhEYf1mj0cY1FH9Zo9GGNRh/WaPTxTon0YZBJX50DfRih0Yc1Gn1cY9GHNRp9WKPRhzUafbxTIn0YZNLX5EAfRmj0YY1GH9dY9GGNRh/WaPRhjUYf75RIHwaZ9LU50IcRGn1Yo9HHNRZ9WKPRhzUafVij0cc7JdKHQSZ9XQ70YYRGH9Zo9HGNRR/WaPRhjUYf1mj08U6J9GGQSV8Otzk4QqMvq9scEzUWfVnd5uAajb6sbnNM7JRI319uc+w+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Created Date (H)" = _t, #"Original Order Type (L)" = _t, #"Sales Order Pool ID (H)" = _t, #"Sales Order ID (H)" = _t, #"D365 SO Link" = _t, #"Original Legal Entity (L)" = _t, #"Sales Responsible Group ID (H)" = _t, #"Product ID (L)" = _t, SnapDate = _t, #"Ordered Qty (L)" = _t, #"Ordered Qty (L) Before" = _t, #"Ordered Qty (L) New" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Created Date (H)", type datetime}, {"Original Order Type (L)", type text}, {"Sales Order Pool ID (H)", type text}, {"Sales Order ID (H)", type text}, {"D365 SO Link", type text}, {"Original Legal Entity (L)", Int64.Type}, {"Sales Responsible Group ID (H)", type text}, {"Product ID (L)", type text}, {"SnapDate", type text}, {"Ordered Qty (L)", Int64.Type}, {"Ordered Qty (L) Before", Int64.Type}, {"Ordered Qty (L) New", Int64.Type}})
in
    #"Changed Type"

 

I am trying to calculate the initial sales order status or Ordered Qty(2nd dataset) and final sales order status Ordered Qty(2nd dataset) 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 Order and Invoiced as final status in all rows.

 

 

As the date range gets filted and selected from 21-03-2023 to 24-03-2023 then the measures should return as below because the min date sales order status is - Open Order which is Initial Status based on the date range and max date's sales order status is  - Delivered which is final status.

 

Here i can move the dates from front to back and back to front, but based on the min date and max date for each sales order id and product id's it should give the respective order status.

The above dax is working fine for me. But it is taking too much time and sometimes it doesnt run and throws that resources are exceeded.

 

Hope i made it clear for you.

I am really looking forward for your right solution for this.

Thanks,

Mohan V.

 

I have tried below dax which is working fine.

 

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'[Sales Order Status Name]), 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))