aggregations are not correct across dates

[electrobrit]

I have 2 tables, one with monthly revenue and another with hard coded revenue by month (Committed and Budget)
I feel like it's something to do with the dates (and relationships) but I can't figure it out.

One table with the key revenue, I have Total measures based on a classication (Total likely, Total Qual or Total unqual) and want it to be a monthly spread (which works with one date)
Then the second part is the committed and budget (seperate table) where it's a monthly spread as well and works when using another date field.

I've created a relationship with the date tables but the committed and budget aggregate the total and show every month then the other totals don't show correctly.

Ultimately, I want the 2 combined and correct. Can someone help with advice on how to set this up to show correctly? I've been going in circles on the relationships. (I have more on the real report so can't lose the relationship with the New Opp and Date table)

here is a sample pbix


Thank you!

[v-yalanwu-msft · Accepted solution]

Hi, @electrobrit ;

You could modify it.

Total Likely = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Order Book"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Order Book")))

Total Qual = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Qualified"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]), 'NEW OPP'[QualGroup]="Qualified"))

Total Unqual = 
IF(HASONEVALUE('Committed (2)'[Month Year]),
CALCULATE(SUM('NEW OPP'[Key Revenue]), FILTER('NEW OPP', 'NEW OPP'[QualGroup]="Unqualified"&&EOMONTH([Date],-1)+1=MAX('Committed (2)'[Month Year]))),
CALCULATE(SUM('NEW OPP'[Key Revenue]),  'NEW OPP'[QualGroup]="Unqualified"))

Then create a new table.

Then create a measure.

Measure = 
IF(ISINSCOPE('Table'[New]),
SWITCH(MAX('Table'[New]),
"Committed",SUM('Committed (2)'[Committed]),
"Budget",SUM('Committed (2)'[Budget]),
"Total Likely",[Total Likely],
"Total Qual",[Total Qual],
"Total Unqual",[Total Unqual]),
SUM('Committed (2)'[Committed])+SUM('Committed (2)'[Budget])+[Total Likely]+[Total Qual]+[Total Unqual])

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.