Get previous date where values in other columns match

[Mike99Schmidt]

I have a union table that was created from two summary tables from separate queries.

The columns that I currently have is Site, Dept, Date. I need to create an additonal column that calculates the previous date in the date column where the site and dept match so we can analyze the shrink per day for the department. Below is a sample of what the table will look like once I can figure out how to calculate the previous date column. Thanks for any assistance on this!!

Site	Dept	Date	O/s	Last Date	Date Diff
163	Packaged Beverage	10/21/2019	6	9/8/2019	43
165	OTP	10/20/2019	7	6/8/2019	134
166	Snacks	10/20/2019	3	5/4/2019	169
163	Packaged Beverage	9/8/2019	4	4/1/2019	160
165	OTP	6/8/2019	1	3/1/2019	99
166	Snacks	5/4/2019	2	2/1/2019	92

[Nathaniel_C · Accepted solution]

@Mike99Schmidt ,

Try this:
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Last date calc = 
var _cDate ='Table'[Date]

var _calc = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Site],'Table'[Dept]),'Table'[Date]<_cDate))

return _calc

[Mike99Schmidt]

That worked perfectly thanks!!!!

@Nathaniel_C 

[Nathaniel_C]

You are welcome!

Nathaniel

[Nathaniel_C]

PBIX MY FILE