Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Akbera1
Frequent Visitor

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

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture1.png

 

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

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
msarmento
Frequent Visitor

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:
 
msarmento_0-1625178730622.png

 

Jihwan_Kim
Super User
Super User

Picture1.png

 

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

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

Regards,

SMA 

Akbera1
Frequent Visitor

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  

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.