DAX calculation in model with many to many relationship

[RossB]

Hi,

I am looking to create a measure that calculates the total estimated weekly cost for a specific financial year, and display this for care packages that are open at the end of a month.

CarePackageLineItem table contains data on care packages, when they start and when they end.  The table contains one row per care package line item (CPLI).  A client may have multiple different CPLI's at the same time.  A CPLI can be open at the end of many reporting periods and contribute to budget commitments in many financial years.

CPLI Active_ReportingPeriodEnd table contains the last day of each month and which CPLI's are active for each of these reporting periods.

BudgetCommitmentCPLIDetail contains each financial year, the CPLI active during these financial years and the budget commitment for each CPLI in that financial year

For each client I am looking to SUM(BudgetCommitmentCPLIDetail[EstimatedWeeklyCost] and display this by CPLI Active_ReportingPeriodEnd[Reporting Last day of month].  So if a client has one or more care package line items active at month end (they can have more than one) then the BudgetCommitmentCPLIDetail[EstimatedWeeklyCost] for the relevant BudgetCommitmentCPLIDetail[FinancialYear_BudgetCommitmentDetail] will be aggregated.  Something like this...

The problem is some CPLI's appear in multiple financial years so the visual is showing CPLI's active at month end okay but is summarising each financial years budget commitment, in this case 2018/19 and 2019/20.  

What i actually need is highlighted in yellow, but should be on one row, without BudgetCommitmentCPLIDetail[FinancialYear_BudgetCommitmentDetail] being displayed.

I think i need some way of clarifying which Active_ReportingPeriodEnd[Reporting Last day of month] fall into which BudgetCommitmentCPLIDetail[FinancialYear_BudgetCommitmentDetail].  I therefore, created the following measure:-

TotalEstimatedWeeklyCost = CALCULATE(SUM(BudgetCommitmentCPLIDetail[EstimatedWeeklyCost]),USERELATIONSHIP('CPLI Active_ReportingPeriodEnd'[Reporting Year],BudgetCommitmentCPLIDetail[FinancialYear_BudgetCommitmentDetail]))

This gets me part way there as now only the BudgetCommitmentCPLIDetail[EstimatedWeeklyCost]) for the relevant BudgetCommitmentCPLIDetail[FinancialYear_BudgetCommitmentDetail] are being summed.  However, this measure is over riding whether the CPLI is active Active_ReportingPeriodEnd[Reporting Last day of month].  

In the example above there are two CPLI items for this client, but both are not active at Active_ReportingPeriodEnd[Reporting Last day of month] until October 2018 (see value change in image 3 above).  How do i keep the filters on the measure that ensure only CPLI's that are active at last day of the reporting period are summed?

I realised I may be totally off the mark and may need to approach it completely differently.  Any help would be much appreciated.

Thank you in advance.

Kind regards,

Ross

[daxer-almighty]

@RossB 

Sorry to say that but this model is totally wrong. You'd better throw it away before it starts producing numbers nobody will be able to understand (I'm sure it does it already but you can't spot it). I'll give you a link to a YT video by the DAX Master Alberto Ferrari.

Please watch it carefully and then think about your model really hard. About your new model because this one is not usable.

https://youtu.be/x3m7qzsVJqQ

[RossB · Accepted solution]

Thanks for replying to my post and the link to the video, much appreciated.  I can see why you would think the model is totally wrong given the number of tables in the screenshot with the cross filter direction set to both.  

The 3 tables 'CPLI Active...' are actually filter tables that have been created in Power Query using a function.  The parameters are complex and I'm not sure I can create such a filter in DAX with a date table (although anythings possible if you give it enough thought).  At the end of the day DAX filters are essentially tables and with these being terminal tables in the model they don't create ambiguity, so I begrudgingly opted for this method.

I do appreciate though this is messy and I would not release this for others to develop reports from.

Managed to resolve my issue in the end...I had considered filter context but not row context.

TotalEstimatedWeeklyCost =

SUMX(BudgetCommitmentCPLIDetail,

CALCULATE(

SUM(BudgetCommitmentCPLIDetail[EstimatedWeeklyCost]), USERELATIONSHIP(BudgetCommitmentCPLIDetail[FinancialYear_BudgetCommitmentDetail],'CPLI Active_ReportingPeriodEnd'[Reporting Year])

)

)

Thanks 

Ross