Forum Discussion

Akbera1's avatar
Akbera1
Icon for Helper I rankHelper I
5 years ago
Solved

stuck in an issue

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

 

 

Need Help

4 Replies

  • Akbera1's avatar
    Akbera1
    Icon for Helper I rankHelper I
    5 years ago

    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  

    • Akbera1's avatar
      Akbera1
      Icon for Helper I rankHelper I
      5 years ago

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

      Regards,

      SMA 

  • msarmento's avatar
    msarmento
    Frequent Visitor
    5 years ago

    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: