Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Need to write DAX to calculate Qty , IF Table1.Flag=1 and Table2.Date>=Table1.Date then sum(Qty) ELSEIF Table1.Flag=0 and Table2.Date<Table1.Date then sum(Qty) ELSE sum(Qty)
Joins based on CODE
Table1:
Code | Name | Group | Org | SKU | method | Flag | Date |
561 | KUM | KUM | 1080 | MB5 | SO Portal | ||
363 | Syn | Syn | 1020 | MB5 | Manual | 0 | |
363 | Syn | Syn | 1020 | MB5 | EDI_RPA | 1 | 9/13/2022 |
259 | TH | TH | 1010 | MB5 | EDI_RPA | ||
126 | PBR | PBR | 1020 | MB5 | SO Portal | ||
127 | BR | BR | 1020 | MB5 | EDI_RPA | 0 | |
127 | BR | BR | 1020 | MB5 | SO Portal | 1 | 10/3/2022 |
Table2:
Code | Name | Group | Org | SKU | Date | Qty |
561 | KUM | KUM | 1080 | MB5 | 10/3/2022 | 50 |
363 | Syn | Syn | 1020 | MB5 | 7/23/2022 | 25 |
363 | Syn | Syn | 1020 | MB5 | 10/24/2022 | 75 |
259 | TH | TH | 1010 | MB5 | 7/25/2022 | 60 |
126 | PBR | PBR | 1020 | MB5 | 10/5/2022 | 200 |
127 | BR | BR | 1020 | MB5 | 9/17/2022 | 65 |
127 | BR | BR | 1020 | MB5 | 10/6/2022 | 50 |
127 | BR | BR | 1020 | MB5 | 10/21/2022 | 50 |
Result:
Code | Name | Group | Org | SKU | method | Qty |
561 | KUM | KUM | 1080 | MB5 | SO Portal | 50 |
363 | Syn | Syn | 1020 | MB5 | Manual | 25 |
363 | Syn | Syn | 1020 | MB5 | EDI_RPA | 75 |
259 | TH | TH | 1010 | MB5 | EDI_RPA | 60 |
126 | PBR | PBR | 1020 | MB5 | SO Portal | 200 |
127 | BR | BR | 1020 | MB5 | EDI_RPA | 65 |
127 | BR | BR | 1020 | MB5 | SO Portal | 100 |
Solved! Go to Solution.
Hi @abchandru ,
According to your description, I made a sample and here is my solution.
Create a calculated column.
Column =
VAR _a =
MAXX ( FILTER ( 'Table1', [Code] = EARLIER ( Table1[Code] ) ), [Date] )
VAR _b =
SUMX (
FILTER (
'Table2',
'Table2'[Code] = EARLIER ( Table1[Code] )
&& 'Table2'[Date] <= _a
),
[Qty]
)
VAR _c =
SUMX (
FILTER (
'Table2',
'Table2'[Code] = EARLIER ( Table1[Code] )
&& 'Table2'[Date] > _a
),
[Qty]
)
VAR _d =
SUMX ( FILTER ( 'Table2', 'Table2'[Code] = EARLIER ( Table1[Code] ) ), [Qty] )
RETURN
SWITCH ( Table1[Flag], 0, _b, 1, _c, BLANK (), _d )
Final output:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@abchandru , If possible merge these two tables in the power query, you can select multiple columns while doing the merge
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
then you can easily create that as column or use in the filter in measure
Hi Amit,
Thanks for reply,I am new to PowerBI tried to merge getting more records and how to write dax to achive
Hi @abchandru ,
According to your description, I made a sample and here is my solution.
Create a calculated column.
Column =
VAR _a =
MAXX ( FILTER ( 'Table1', [Code] = EARLIER ( Table1[Code] ) ), [Date] )
VAR _b =
SUMX (
FILTER (
'Table2',
'Table2'[Code] = EARLIER ( Table1[Code] )
&& 'Table2'[Date] <= _a
),
[Qty]
)
VAR _c =
SUMX (
FILTER (
'Table2',
'Table2'[Code] = EARLIER ( Table1[Code] )
&& 'Table2'[Date] > _a
),
[Qty]
)
VAR _d =
SUMX ( FILTER ( 'Table2', 'Table2'[Code] = EARLIER ( Table1[Code] ) ), [Qty] )
RETURN
SWITCH ( Table1[Flag], 0, _b, 1, _c, BLANK (), _d )
Final output:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |