Code & Logic seemingly perfect - wrong result output

[JoeGough]

Hi All,

Here I present what I deem to be perfect code, which should work flawlessly, and bear no errors. 

But... I wouldn't be here if that was the case. The logic checks out and all the parameters work, yet as if being sabotaged by some mischievous data-ghost, the ironclad laws of Power BI are broken. Call me superstitious. 

If someone could tell me what I've done wrong I'd be gripped to know as these KPIs have a deadline of 2 days and I've spent 4 hours cringing and raging in frustration over one analysis field.

I am attempting to change a category of an account based on age at any given time, dynamically adjusted for the slicer range, which I can then use as a slicer in itself (thus it cannot be a measure).

Here are my variables:

(Measure) FirstSlicerDate =

CALCULATE(

    MIN(InvoiceLedger[Date]),

    REMOVEFILTERS(InvoiceLedger[A/C Code]), 

    ALLSELECTED(InvoiceLedger))

//Returns Oldest date of invoice occurring within slicer range

(Measure) LatestSlicerDate =

CALCULATE(

    MAX(InvoiceLedger[Date]),

    REMOVEFILTERS(InvoiceLedger[A/C Code]), 

    ALLSELECTED(InvoiceLedger))

// Returns most recent date of invoice occurring within slicer range

(Measure) AccountAge =

VAR InvoiceStartDate = SELECTEDVALUE('Account'[Invoice Start Date])

RETURN

    IF(

        ISBLANK(InvoiceStartDate),

        BLANK(),

        DATEDIFF(InvoiceStartDate, [LatestSlicerDate], DAY))

// Returns column 'Invoice Start Date', which is hard written against Account Code in raw data, then determines the date difference between this and LatestSlicerDate, to output an age of a given account at a point defined by slicer 

(Column) AccountCategory =

    VAR IsEmbedded = [AccountAge] >= 8 && [AccountAge] <365

    VAR IsLikeForLike = [AccountAge] >= 365

    VAR IsNew = [AccountAge] < 8 || [Invoice Start Date] > [FirstSlicerDate]

    RETURN

        SWITCH(

            TRUE(),

            ISBLANK([AccountAge]), "Unknown",

            IsEmbedded, "Embedded",

            IsLikeForLike, "Like for Like",

            IsNew, "New",

            "Unknown")

// This codes for sorting a given account into its category by age, defined by the variables above. Note here the switch order is correct. As you can see I was really trying to get this one to work somehow, hence I've added 3 totally unnecessary variables.

//I have then set up debug measures which are as follows;

debug_IsEmbedded = [AccountAge] >= 8 && [AccountAge] <365

debug_IsLikeForLike = [AccountAge] >= 365

debug_IsNew = [AccountAge] < 8 || [Invoice Start Date] > [FirstSlicerDate]

 // In hindsight I could have integrated this into AccountCatagory to be more tidy, but whatever; here we are.

My parameters for the following example are; 'A/C Code' = ABBOBG, 'Invoice Start Date' = 06/01/24, slicer range is set so FirstSlicerDate = 13/01/24, and LatestSlicerDate = 20/01/24. (Year/Month/Day are 'Invoice Start Date')

As you can see, all parameters except AccountCatagory are calculated correctly. the expected output here is 'Embedded', following the logic, 8<= AccountAge <365, where AccountAge is 14. 

Debugging this, we are presented with the following;

This blatantly contradicts the actual output.

Please can someone advise on this.

Thanks in advance 😀

[DataInsights]

@JoeGough,

It appears your calculated column is based on measures. Calculated columns don't recognize user-specified filters such as slicers, which may explain the unexpected results.

[JoeGough]

Thanks, is there a way around this? or a different way to achieve my goal?

[DataInsights]

@JoeGough,

You could create a disconnected AccountCategory table with one row per category. Then use this table in a visual and use DAX (measure) to control filtering. Your calculated column logic could be adapted to work in a measure. Are you able to post a link to a sample pbix (OneDrive, etc.)?