Forum Discussion

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

DATESINPERIOD with different tables

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

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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])

     

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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.