Calculated Column Filter Issue

[gsed99]

Hello,

I created a calulated column, which worked fine, but now I want to add a filter to that same column, and I am getting the error: "A single value for 'SAODate' in table 'Marketing Snapshot' cannot be determine. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation.

I got this error when I tried making a measure, but don't understand why I am getting it when I am using a column.

Any help would be appreciated!
Thanks,

Greg

Column prior to Filter:

SAO Aging (Today) = DATEDIFF('Marketing Snapshot'[SAODate],TODAY(),DAY)

Current formula:

SAO Aging (Today) = CALCULATE(DATEDIFF('Marketing Snapshot'[SAODate],TODAY(),DAY),
FILTER (
ALL ( 'Marketing Snapshot'[SAOYear] ),
'Marketing Snapshot'[SAOYear] = [Max SAO Year]
),
FILTER('Marketing Snapshot','Marketing Snapshot'[OpportunityOrigin]="Marketing"),
FILTER (
ALL ( 'Marketing Snapshot'[DateStampMonth] ),
'Marketing Snapshot'[DateStampMonth] = [Max DateStampMonth]
))

[TomMartens]

Hey,

if i understand your requirement correctly you are creating the Calculated Column "SAO Aging (Today)"

By default there is just a ROW CONTEXT and no FILTER CONTEXT.

Using CALCULATE means that an existing ROW CONTEXT is transformed into FILTER CONTEXT

Actually I'm not sure what 

[Max SAO Year] and [Max DateStampMonth] are, if they are measures, it's not possible what you are trying, due to the fact that measures are not available during the creation of CALCULATED COLUMNS.

If they are columns inside the table where you define the CALCULATED COLUMN you have to use some kind of Aggregate Function around it like MAX.

Please be aware that using CALCULATE creates a FILTER CONTEXT, inside this FILTER CONTEXT there is just one available - the current row.

Hope this gives you an idea and gets you started.

It would also being helpful if you create some sample data, upload the pbix to onedrive or dropbox and share the link to that pbix.

Regards

Tom

[gsed99]

@TomMartens

Thank you for that information!

I am thinking to simply this and tried to use an origional field that is part of the database in the filter, but got the same error as before.

I am basically trying to do the DATEDIFF calculation, but only on filtered data, as I want to only look at a subset.,

For this example I only want to look at data with SAOYear=2018, and each record's SAO Date difference from today, but aggregated and averaged.

Would something like this work?

SAO Aging (Today) = CALCULATE(DATEDIFF('Marketing Snapshot'[SAODate],TODAY(),DAY),
FILTER (
ALL ( 'Marketing Snapshot'[SAOYear] ),
'Marketing Snapshot'[SAOYear] = MAX('Marketing Snapshot'[SAOYear])
))

[TomMartens · Accepted solution]

Hey,

I guess this would also calculate the value for each but just based on the values of the year 2018.

For this reason I would do the following
SAO Aging (Today) = 
IF(YEAR([SAODate]) = YEAR(TODAY())
,your calculation

,BLANK()
)

[gsed99]

@TomMartens

Thanks that worked!