Trailing 12 month count error -single value for column 'Date' in table'Date' cannot be determine

[PhilSmith]

OK I have two tables. Relevant here are: 'Item'[item_id],'Item'[activedate] (where each item_id is distinct/unique,) and 'Date'[date].
I have an inactive relationship between Item[activedate] and 'Date[date] just for this measure. It's inactive because 'Item'[item_id] is also related to a Sales table which is in turn related to 'Date[date], and it creates a conflict.

I am trying to create a 12 month trailing count of items, based on this 'Item'[activedate] for a rolling visual. For any given date, I want a count of all items with an active date between the given date and a date one year before the given date.  I have:

SKUCreation_12month_rollingCount = CALCULATE(count('Item'[item_id]),USERELATIONSHIP('Item'[activedate],'Date'[Date]),DATESBETWEEN('Item'[activedate], DATEADD('Date'[Date], -1, YEAR),'Date'[Date]))

Yields the error "A single value for column 'Date' in table'Date' cannot be determined..."

Any help is appreciated.

Thanx

Phil

[PhilSmith]

Lets change it to a 7 Day trailing count for simplicity.  I can extrapolate from there.  Remember the inactive relationship Item-Date, created that is different then the active relationship between Item-Sales, then Sales-Date.

The first table is a sample of data points in Item. The second table would be a table visual of the results, with Date and the measure I am trying to get to, 7DayTrailingCount. For any particular date, how many items have a active date in that 7 day trailing window from that Date. If the date is 1/8, then you would count both items with a activedate of 1/5, and the item with aactivedate of 1/3. If the date is 1/6, you would count both items with a activedate of 1/5, as well as 1/3 and 1/1.

Item Table		Results Visual
Item ID	Activedate		Date		7DayTrailingCount
1234	1/1/2023		1/1/2023		1
1235	1/3/2023		1/2/2023		1
1236	1/5/2023		1/3/2023		2
12437	1/5/2023		1/4/2023		2
12438	1/15/2023		1/5/2023		4
12439	1/19/2023		1/6/2023		4
			1/7/2023		4
			1/8/2023		3
			1/9/2023		3
			1/10/2023		2
			1/11/2023		2
			1/12/2023		0
			1/13/2023		0
			1/14/2023		0
			1/15/2023		1
			1/16/2023		1
			1/17/2023		1
			1/18/2023		1
			1/19/2023		2

I hope that clears things up?

Thanx

Phil

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[PhilSmith]

Thank you.  That directed me to the fix.

Phil

[Ashish_Mathur]

You are welcome.

[amitchandak]

@PhilSmith , if you need training 12 month

CALCULATE(CALCULATE(count('Item'[item_id]),USERELATIONSHIP('Item'[activedate],'Date'[Date])), DATEADD('Date'[Date], -1, YEAR))

for rolling 12

Rolling 12 = CALCULATE(CALCULATE(count('Item'[item_id]),USERELATIONSHIP('Item'[activedate],'Date'[Date])),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

[PhilSmith]

Thank You.  You are correct, Trailing 12 months would be the more apt term for the measure here.    Both of these measures work, in that I do not get any errors, but neither of them supply any actual data.  If I line them up on a clustered column chart next to item_id (count) on the visual, I get counts over days as I would expect, but nothing surfaces for either measure.

[Ashish_Mathur]

Hi,

Very confused about what you want.  Share some data, describe the question and show the expected result.