EXCEPT() not working as intended

[Anonymous]

Hi,

Im trying to determine a list of customers who ordered last year but not this year, and get the PnL loss from it.

For calculating the actual list of customers, formula is working fine:

Non reco_# = 
VAR YAG = SUMMARIZE(
    CALCULATETABLE(
        Overview,
        ALL('Date'[Date]),
        SAMEPERIODLASTYEAR('Date'[Date])
    ),
    Overview[Customer]
)
VAR CurrentFiscal = SUMMARIZE(
    Overview,
    Overview[Customer]
)
RETURN
COUNTROWS(EXCEPT(YAG,CurrentFiscal))

However, for calculating the PnL impact, using this formula

Non reco_€ = 
VAR YAG = SUMMARIZE(
    CALCULATETABLE(
        Overview,
        --ALL('Date'[Date]),
        SAMEPERIODLASTYEAR('Date'[Date])
    ),
    Overview[Customer],
    "NISx", SUM(Overview[Net Invoice Sales])
)
VAR CurrentFiscal = SUMMARIZE(
    Overview,
    Overview[Customer],
    "NISx", SUM(Overview[Net Invoice Sales])
)
RETURN
SUMX(EXCEPT(YAG,CurrentFiscal),[NISx])

I then get the PnL for both clients who ordered last year and not this year (what i want), but also for clients who ordered BOTH years... despite using EXCEPT...

Any clue why?

Thanks in advance !

[amitchandak]

@Anonymous , is there any date selected on your page or calendar ending this year.

Make sure date is there so that it takes the correct value of this and last year

Or like datesytd like given in the example

This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

[Anonymous]

Hi @amitchandak ,

This is a slicer on my page, with a whole fiscal year selected (for my company, going from 01/07/2019 to 30/06/2020).

As explained, formula for number of clients who didnt order works fine with this setup, it just bugs when i want the € amount linked to those customers. Please see below a screenshot: (NIS YAG refers to Net Invoice Sales for Year AGo, while Net Invoice Sales is just the PnL for this year, as the slicer allows me to display it without the need of a measure)

[Anonymous · Accepted solution]

As I couldnt sum with Except, i found this turnaround that works:

Non reco_€ = 
VAR YAG = SUMMARIZE(
    CALCULATETABLE(
        Overview,
        SAMEPERIODLASTYEAR('Date'[Date])
    ),
    Overview[Customer]
)
VAR CurrentFiscal = SUMMARIZE(
    Overview,
    Overview[Customer]
)
VAR NonReco = EXCEPT(YAG,CurrentFiscal)
RETURN
CALCULATE(
    SUM(Overview[Net Invoice Sales]),
    FILTER(NonReco, Overview[Customer]),
    SAMEPERIODLASTYEAR('Date'[Date])    
)