Forum Discussion
Group by with join on 2 columns
Hi,
What's the best way to achieve the below in PowerBI (using merge is considered). Effectively using aggregates with group by and joining across 2 tables (Contracts and Receipts).
SELECT c.Age, c.ProductionDate, SUM(r.HT)
FROM Contracts c
JOIN Receipts r ON c.ContractRef =r.ContractRef AND EOMONTH(c.ProductioNDate) = EOMONTH(r.TransactionDate)
WHERE c.Status = 'AFN'
AND c.EffectiveDate <= c.ProductionDate
AND r.Status = 'P'
GROUP BY c.AGE, c.ProductionDate
Thanks
Hi Anonymous ,
Please refer to below measure and see if the result achieve your expectation.
Measure = VAR a = CALCULATE ( SUM ( Transactions[HT] ), FILTER ( Transactions, EOMONTH ( Transactions[TransactionDate], 0 ) = EOMONTH ( MAX ( Contracts[ProductionDate] ), 0 ) && Transactions[Status] = "P" && MAX ( Contracts[ETAT] ) = "AFN" && MAX ( Contracts[EffectiveDate] ) <= MAX ( Contracts[ProductionDate] ) ) ) RETURN IF ( ISBLANK ( a ), "-", a )Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- JarroVGIT
Resident Rockstar
It would really help not to give some SQL statement but actually to give the table structure, some sample data and the desired outcome based on the sample data you provide. That way, we can help you out on this issue 🙂
Hi Anonymous ,
You could try creating relationship between "Contracts" and "Receipts", then create a measure like below:
CALCULATE(SUM(HT),FILTER(ALLEXCEPT('Contracts','Contracts'[AGE],'Contracts'[ProductionDate]),"Conditions"))The measure above is for reference only, cannot be verified without data support.
Please share some sample data to us if you don't have any Confidential Information.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your replies. Please refer to sample data below:
Contracts (c)
ContractRef ETAT EffectiveDate ProductionDate AGE ABC AFN 04/03/2014 01/06/2019 3 DEF AFN 16/06/2010 01/06/2019 2 GHE AFN 23/11/2011 02/07/2019 2 HIJ DEF 01/05/2017 01/06/2019 2 KLM AFN 09/09/2011 01/06/2019 1 Transactions
ContractRef HT TransactionDate Status ABC 154.79 05/06/2019 P ABC 427.84 02/06/2019 P GHE 278.66 05/07/2019 P HIJ 363.35 04/06/2019 P KLM 225.44 05/06/2019 P Result
Age ReportingDate SUM HT 1 01/06/2019 225.44 2 01/06/2019 - 2 01/07/2019 278.66 3 01/06/2019 582.63 Appreciate if the solution could be displayed as the table above (i.e. not simply a dax expression to calculate the aggregate?)
Thanks in advance.
Hi Anonymous ,
Please refer to below measure and see if the result achieve your expectation.
Measure = VAR a = CALCULATE ( SUM ( Transactions[HT] ), FILTER ( Transactions, EOMONTH ( Transactions[TransactionDate], 0 ) = EOMONTH ( MAX ( Contracts[ProductionDate] ), 0 ) && Transactions[Status] = "P" && MAX ( Contracts[ETAT] ) = "AFN" && MAX ( Contracts[EffectiveDate] ) <= MAX ( Contracts[ProductionDate] ) ) ) RETURN IF ( ISBLANK ( a ), "-", a )Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
