stuck in an issue

[Akbera1]

Hey,

Guys i am stuck, the scenario is,

Transactions are like this.

SalesID                 DC No.            OrderQty      Qty           Date and time 

So-000001            DC-000001        100            95              06/25/2021 

So-000001            DC-000002        100            05              06/26/2021 

So-000002            DC-000003          20             20             06/26/2021 

So-000003            DC-000004          25            25              06/27/2021 

So-000004            DC-000005        200           100              06/27/2021 

So-000004            DC-000006        200           100             06/27/2021 

 I want the qty of 1st and 2nd DCs in a column. DC prioritize according to date.

TIA,

SMA

[msarmento]

If I got your problem right, you could just create a calculated column to create a new index for DC by Sales ID and use it on a Matrix visual.

Here is the DAX that I created for this column:

DC Ordered =

VAR CurrentSalesID = [SalesID]

VAR CurrentDate = [Dateandtime]

RETURN

"DC - " &

CALCULATE(

DISTINCTCOUNT([DCNo]),

FILTER(

ALL('Table'),

'Table'[SalesID]=CurrentSalesID

&& 'Table'[Dateandtime] <= CurrentDate

)

)

And here is a matrix using this new calculated column:

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/s69d6dyocn8wr7k/akbera.pbix?dl=0 

[Akbera1]

Thanks alot dear.. that is exactly what i am looking..

Regards,

SMA 

[Akbera1]

SO numbers are sales order nos and DC are there despatch documents i want it like 

SalesID           Ordered Qty      1stDC      2nd Dc 

So-000001       100                     95            05