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.
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
Solved! Go to Solution.
Hi @kurtazzopardi ,
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.
Hi @kurtazzopardi ,
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 @kurtazzopardi ,
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.
This is interesting. Thanks.
What i have noticed however is that to filter on ETAT in the measure (line below), the ETAT column needs to be selected and showing in the grid. If i simply include this filter in the measure and omit the ETAT, then measure would return a 0 value for all rows.
Why is this? My question leads me to the correct understanding of MAX in this context. I've tried to understand the function by reading forums and documentation, but still seem to be confused on the purpose of MAX when referncing columns from other tables in an measure (as the case for Contracts table). Would you be able to clarify please?
p.s. i also had to apply a relationship between CONTRAT columns in both tables to get correct results
&& MAX ( Contracts[ETAT] ) = "AFN"
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 🙂
Proud to be a Super User!
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |