Using 30 day trailing avg measure in linked Table

[troyhimes]

See file in dropbox link.....30DAY TRAILING AVG QUESTION 

I am generating a 30 day trailing average for a table with several entries/per day.

1st Table ("Data input")

Date	pt+pd	Area	Region
1/2/2020 12:00:00 AM	0.290538161993027	BLITZ	BLITZ
1/2/2020 12:00:00 AM	0.193269044160843	BLITZ	BLITZ
1/3/2020 12:00:00 AM	0.432600080966949	crusher	Crusher
1/3/2020 12:00:00 AM	0.415469497442245	crusher	Crusher
1/3/2020 12:00:00 AM	0.2790667116642	crusher	Crusher

I have a second table that is linked to the first using the date column with single entries for each date.  I would like to use the 30 day trailing avg for each day to multiply by another column in the second table .  Not sure how to get this done.  My first try I attempted by creating a calculated column in the first table for the 30 day trailing avg, but I wasn't able to get it to calculate correctly.  Do I need a calc'd column or can I get it done with a measure?

2nd Table ("Concentrator_Final")

DATE	Mill Feed Tons	CALC EXAMPLE
1/1/2020 12:00:00 AM	1813.9000001	1/1 30day_avg*1813.9
1/2/2020 12:00:00 AM	1897.32	1/2 30day_avg*1897.32
1/3/2020 12:00:00 AM	2877.02	1/3 30day_avg*2877.02

[Greg_Deckler]

OK, so step 1 is to get a working 30 day trailing average, correct?

[troyhimes]

Perhaps I didn't make it clear in my post....I already have a measure calculating the 30-day trailing avg, what I'm looking for is the ability to use that average in the second table as indicated.

[troyhimes]

To clarify my original question further, I was asking if I could use that 30 day trailing avg. measure for my calculation in the second table or if I need to use a calculated column in the first table (which, if required, I don't know how to do).

[Greg_Deckler]

OK, so first, that link points to an empty Excel file with just a Sheet2 tab...that is empty.

Second, you want to use RELATEDTABLE or LOOKUPVALUE to retrieve the value you want in the other table.

[troyhimes]

@Anonymous Yep, that makes sense. I've changed the relationship and added the measure "30 Day Rolling Avg(BTZ)test".  Which gets me closer.  It now appears that my original 30 day rolling avg measure isn't yielding the results I require on day by day basis.  My method works when filtered for a single date but not for each day.

30 Day Rolling Avg:=calculate(average('Data input'[PtPd]),DATESINPERIOD('Data input'[DATE],LASTDATE('Data input'[DATE]),-30,DAY))

@Greg_Deckler  See the updated link.  Isn't a cacluated column required in Table 1 ("Data Input") to use RELATEDTABLE or LOOKUPVALUE in Table 2 ("Concentrator_Final)?  I was trying to avoid a calculated column but perhaps this is a method that fits my needs better (?).

30 DAY-TRAILING-AVG-QUESTION_UPDATED.xlsx 

[troyhimes · Accepted solution]

It looks like the answer was simpler than I realized, my filter logic in the Calculate function was not correct.  Rather than filtering using dates from the fact table ('Data input'[DATE]), I should be using dates from the dimension table (Conccentrator_Final[Date]).  Thanks @Anonymous  and @Greg_Deckler for your input.

ORIGINAL

30 Day Rolling Avg:=calculate(average('Data input'[PtPd]),DATESINPERIOD('Data input'[DATE],LASTDATE('Data input'[DATE]),-30,DAY))

MODIFIED

30 Day Rolling Avg: =CALCULATE(AVERAGE('Data input'[PtPd]), DATESINPERIOD(Conccentrator_Final[Date],LASTDATE(Conccentrator_Final[Date]),-30,DAY))

[Greg_Deckler]

@troyhimes yes, that would be in a DAX calculated column. 

[troyhimes]

After thinking about it, unless I'm missing something, I'm not sure a calculated column would work for the following reason.....

In Table 1 ("Data Input") there won't necessarily be data for every day, but there will always be data each day in Table 2 ("Concentrator_Final").  I will require that the "latest" 30 day avg. value from Table 1 is multiplied by the "latest" date data in Table 2.  For example, the last date of data I have in Table 1 is from 2/18/20 so the 30 day avg will reach back to 1/19/20, which will be multiplied by the data in Table 2 from 2/18/20, 2/19/20, 2/20/20, etc.   I don't have a common key to link the values using LOOKUPVALUE or RELATEDTABLE.

[Anonymous]

You use the measure in the table that stores unique dates. This table should be a dimension table connected via 1:* to the fact table.

Best
D