Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Table T1 is Sales data, and Table T2 is Delivered data, both are Fact table.
Solved! Go to 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.
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.
@v-diye-msft Hi Diye, May I get your insight based on my reply? thanks a million.
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.
@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 ?
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 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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |