DAX help needed when dealing with multiple bridge tables (many to manys) and multiple dates

[amylewisAZ]

Hi All,

I am struggling to get the correct DAX when 

Data Model:

Formulas:

-- Awarded Grants Counts
Total Grants Awarded = CALCULATE(DISTINCTCOUNT('Grant Award Info'[GrantID])
,USERELATIONSHIP('Date'[Date], 'Grant Award Info'[AwardDate])
)

TotalDonorBudgetApproved = CALCULATE(SUM('Grant Award Info'[DonorLOABudget]),USERELATIONSHIP('Date'[Date], 'Grant Award Info'[AwardDate]))

-- Not Awarded:

Total Grants NotAwarded = CALCULATE(
DISTINCTCOUNT('Grant Pipeline History'[GrantKey]),
FILTER (ALL('Grant Pipeline History'[Grant Status]),
'Grant Pipeline History'[Grant Status]= "Not Awarded"),USERELATIONSHIP('Date'[Date],'Grant Pipeline History'[Status Date]))

Total Not Awarded $ = CALCULATE(
SUM('Grant Submission Info'[TotalDonorBudgetSubmitted]),
FILTER (ALL('Grant Pipeline History'[Grant Status]),
'Grant Pipeline History'[Grant Status]= "Not Awarded"),USERELATIONSHIP('Date'[Date],'Grant Pipeline History'[Status Date]))

Issue:

here you can see the issue... top one is the totals with only the GratDetail.Domain (no many to many relationships.... 2nd one is with Region and the totals... you can see the grand totals are correct, but the details are not. last one is area and same issue.

Any Advice?

Thank you in advance,

Amy

[Anonymous]

Hello @amylewisAZ 
Can you please share your pbix file after removing the sensitive data?