Need help No delivered sales amount (Per transaction Sales data great than Delivered)

[Anonymous]

Table T1 is Sales data, and Table T2 is Delivered data, both are Fact table.

1. Table T1 and T2 join with ID,
2. Many to Many relationship
3. Want to show the total sales amount of no delivered product by date.
4. The most hard part is:  I need add a filter to the ID which is sales amount is greater than Delivered amount.

[v-diye-msft]

Hi @Anonymous 

Let me know if you'd like to get below results:

Measure for total sales amount of no delivered product:

Measure = IF(CALCULATE(MAX('Table (2)'[Deliv_Date]),FILTER('Table',[ID]=MAX('Table (2)'[ID])))<>BLANK(),0,MAX('Table'[Sales_Amt]))

TotalsalesofNoDiliveryDate = SUMX('Table',[Measure])

Calculated column for the ID slicer:

IDLIST = IF([Sales_Amt]>MAXX(RELATEDTABLE('Table (2)'),[Sales Amt]),[ID])

I used you sample but added 1 row with ID 123 to make it easier to find the clarification:

PBIX attached.

[Anonymous]

@v-diye-msft Hi Diye, May I get your insight based on my reply? thanks a million.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

I modified the measure as below to calculate the cumulative amounts of undelivered sales: (Other measures kept unchanged)

TotalsalesofNoDiliveryDate = SUMX(FILTER(ALL('Table'),[Tran_Date]<=MAX('Table'[Tran_Date])),[Measure])

pbix attached.

[Anonymous]

@v-diye-msft @Anonymous  Really apprecaite your response and support,

1, This is a many to many relationship, in the Table1, for 1 ID, there are multiple rows.

2, I 'd like to get the cumulative sales amount undelivered,

3, This is kind of like a inventory data, I have incoming data and outcoming data as the time, I 'd like to get the cumulative amount what I have,  but for every ID, It should have the sales amount is great than the delivery amount.

4, I'd like to show the cumulative amount by date.

5, May you please guide me load a PBIX file ?

[Anonymous]

Hi @Anonymous ,

Please post sample data in text format ( i.e: Copy & Pasteable format).

That will make it easier to help you.

Cheers,

Rob

[Anonymous]

@Anonymous Appreciate for your response

I need to get the cumulative Open sales amount:

Table T1 Format is as:

ID	Tran_Date	Sales_Amt
12285319_15	8/2/2019	71.2
11755054_73	8/2/2019	158
12336199_14	8/12/2019	157.43
12445312_14	8/31/2019	157.38
2000493738_100	8/31/2019	-598
12308184_12	8/10/2019	-193.29
12203847_10	8/3/2019	-449
11981361_34	8/30/2019	157.38
12326067_14	8/10/2019	157.38
12263246_50	8/31/2019	-598
2000511216_204	8/9/2019	157.43
12324887_20	8/10/2019	158
12302713_34	8/5/2019	157.38
12325910_20	8/16/2019	-449
12346522_20	8/15/2019	-449

 Table T2:

ID	Deliv_Date	Sales Amt
2000516244_101	8/23/2019	109
2000516244_102	8/23/2019	200
2000516244_103	8/23/2019	130
2000516244_104	8/23/2019	160
2000516434_100	8/27/2019	399
2000516434_200	8/27/2019	499
2000516640_100	8/28/2019	299
2000516275_301	8/30/2019	215
2000516275_102	8/25/2019	332.97
2000516275_200	8/25/2019	60

Here is the SQL:

SELECT SUM(COALESCE(w.[Sales Amt], 0) - COALESCE(d.[Deliv Amt], 0)) Open_Sales
FROM
(select ID, COALESCE(sum([Sales_amt]), 0) [Sales Amt]
from Write_sales 
where [Tran_Date] < @Datefilter 
group by ID ) W
left join
(select ID, COALESCE(sum([Deliv_Amt]), 0) [Deliv Amt]
from Deliv_sales 
where deliv_date < @Datefilter 
group by id) D
on d.ID = w.ID
WHERE w.[Sales Amt] > COALESCE(d. [Deliv Amt], 0)