Table to Measure Relationship Help

[Libbyb23]

Hi!

I have created the following measure, which calculates correctly ( I have created the measure in two ways: with variables and without). But the relationship isnt picking it up it seems to be able to disperse the trend sales by branch:

scheduled, sold, and booked are measures too using different variations of variables but the measures are working correctly with the relationships defined. 

How come trend isnt?

[Anonymous]

Hi @Libbyb23 

Are you calculating Trend based on different classifications? What is the basis for the calculation? Could you please share some sample data and expected results based on sample data? So that I can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account. Thank you for your time and efforts in advance.

Best Regards,
Yulia Xu

[Libbyb23]

Hi, 

I cannot post the pbix, but I can post the measures and screenshots.

The measure formula that works correctly is:

CALCULATE(SUM('Sales Data'[sales_price]),  FILTER('Sales Data','Sales Data'[Month Match] = true && 'Sales Data'[Month if Matching] = [Current Month]-12))/ CALCULATE([# of WorkDays], FILTER('Date Key', [Current Month]-12))

The measure formula I need to use due to some date constraints is:

WORK12MONTH =

  VAR __Today = TODAY()

  VAR __Max = EOMONTH( __Today, -12 )

  VAR __Min = DATE( YEAR( EOMONTH( __Today, -12) ), MONTH( EOMONTH( __Today, -12 )), 1 )

  VAR __Table = FILTER( ALL( 'Date Dimension'), 'Date Dimension'[FullDateAlternateKey] >= __Min && 'Date Dimension'[FullDateAlternateKey] <= __Max )

  VAR __Result = SUMX(__table, [# of Work Days])

RETURN

__Result

The first formula works and allows the breakup between branches to show the trrend per branch the new measure does not. 

The relationships are the same and use the same variables. So I am not sure why it wont work.

[Libbyb23 · Accepted solution]

I got it figured out! The All() was messing with the relationship, but once removed it was fixed.