Count Distinct but ignore any values counted in prior months

[sparamanga]

Hi, Im trying to calculate a distinct count of exporters and display this on a column chart with the snapshot month on the x axis. I want to exclude the exporters already counted in previous months so that each column will add up to the total of unique exporters so far this year of 178.

my code seems to have a distinct count per month but does not exclude any values counted in prior months.. what is wrong with my approach here?

VAR CurrentMonthOrder = selectedvalue('Supported Business'[RR SUP BUS MONTH ORDER])

VAR PreviousMonths =

calculatetable(

 values('Supported Business'[Exporter Name]),

'Supported Business'[RR_SUPPORTED_FY]="2023-2024" &&

'Supported Business'[RR SUP BUS MONTH ORDER] < CurrentMonthOrder

 )

VAR ExportersThisMonth =

calculatetable(

 values('Supported Business'[Exporter Name]),

  'Supported Business'[RR_SUPPORTED_FY]="2023-2024"

    && 'Supported Business'[RR SUP BUS MONTH ORDER] = CurrentMonthOrder

 )

/*

VAR UniqueExportersThisMonth = EXCEPT(

    ExportersThisMonth,

    PreviousMonths

)

*/

var test =

calculate(DISTINCTCOUNT('Supported Business'[Exporter Name]),

'Supported Business'[RR_SUPPORTED_FY]="2023-2024",

not('Supported Business'[Exporter Name]) in PreviousMonths

)

RETURN

test

[sparamanga]

I actually started with the comma syntax.. but as the measure didnt return what i was expecting i tried the && syntax.. ill revert oit back to comma again.. do you think this will now return the correct result?

[sjoerdvn]

no, it propably doesn't mattre that much.
Can't tell from what you've posted why it is not working. Are there other tables involved with an active relationship? for instance a date table? Or are you creating a computed column instead of a measure?

[sjoerdvn]

start by replacing the "&&" with commas in the calculatetable statements.