DATESINPERIOD with different tables

[michael_knight]

Hi,

I'm trying to calculate the average income using dates from a seperate table and the DATESINPERIOD table

These are the tables I've got:

This is my main dataset where I'm able to see how much people are earning per month

This is a dataset which is used as an estimate on how much the employee should be earning over the period of times

I want  to calculate the average of how much they've earnt and compare it to the estimate. I want to create a measure using DATESINPERIOD which will allow me to compare the Actual earning to the Estimated earning during a certain time frame

This is the measure I've got:

Sales Moving Annual Total -Year 1 = 
CALCULATE (
    [Total Earnings],
    DATESINPERIOD (
        OTE[StartDate],
        MAX ( OTE[Year1] ),
        -1,
        YEAR
    )
)

This is the results from the measures, as you can see it doesn't work

And this the relationships I'm using

Any help will be much appreciated

PBIX: https://www.dropbox.com/s/ogqxkft7tb373xn/help%20-%20Copy%20%282%29.pbix?dl=0

Thanks,

Mike

[amitchandak]

@michael_knight , You can add new column/s in second table. (based on what I got so far)

example

Sumx(filter(Table, Table [Date] >= OTE[StartDate] && Table[Date] <= OTE[Year1] ),Table[salary])

[michael_knight]

Hi @amitchandak . That didn't work, do you have any other suggestions?

[v-kkf-msft · Accepted solution]

Hi @michael_knight ,

Please try the following measures:

Rolling average -Year1 = 
VAR __DATE_PERIOD =
DATESINPERIOD (
    'Date'[Full Date],
    MAX(OTE[Year1]) ,
    -1,
    YEAR
)
RETURN
	AVERAGEX(
		CALCULATETABLE( Earnings, __DATE_PERIOD ),
		'Earnings'[Salary] + Earnings[Commission]
	)

Total Rolling average -Year1 = 
IF(
    ISFILTERED(Agents[Agent]),
    [Rolling average -Year1],
    AVERAGEX( ALLSELECTED(Agents[Agent]), [Rolling average -Year1] )
)

Rolling average -Year2 = 
VAR __DATE_PERIOD =
DATESINPERIOD (
    'Date'[Full Date],
    MAX(OTE[Year2]) ,
    -1,
    YEAR
)
RETURN
	AVERAGEX(
		CALCULATETABLE( Earnings, __DATE_PERIOD ),
		'Earnings'[Salary] + Earnings[Commission]
	)

Total Rolling average -Year2 = 
IF(
    ISFILTERED(Agents[Agent]),
    [Rolling average -Year2],
    AVERAGEX( ALLSELECTED(Agents[Agent]), [Rolling average -Year2] )
)

Rolling average -Year3 = 
VAR __DATE_PERIOD =
DATESINPERIOD (
    'Date'[Full Date],
    MAX(OTE[Year3]) ,
    -1,
    YEAR
)
RETURN
	AVERAGEX(
		CALCULATETABLE( Earnings, __DATE_PERIOD ),
		'Earnings'[Salary] + Earnings[Commission]
	)

Total Rolling average -Year3 = 
IF(
    ISFILTERED(Agents[Agent]),
    [Rolling average -Year3],
    AVERAGEX( ALLSELECTED(Agents[Agent]), [Rolling average -Year3] )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.