How calculate quantity difference (distinct count) between row date and selected date

[iamdmitry]

Hello, guys!

Please, help me or give me some advice, how calculate quantity difference (distinct count) between row date and selected date.

For example, if selected date is 31-1-18:

Date       | Distinct Count ID | Diff
31-1-18  | 12                        | 0
01-2-18  | 10                        | -2
02-2-18  | 16                        | 4
03-2-18  | 22                        | 10
04-2-18  | 11                        | -1

Dataset is seems like:

Date | ID | Category | Type | Color | etc.

I need to create chart with Diff as values and Dates as time axis.
If it's possible, I would like to save possibility to use filtering by columns (Category, Type, Color...)

----------------------------------------------------------

I've calculated two measures like:

fixed quantity = calculate(distinctcount(ID), filter(table, Date = Date(2018, 1, 31)))
dynamic quantity = distinctcount(ID)
diff = dynamic quantity - fixed quantity

But that approach doesn't work ;-(

[Greg_Deckler · Accepted solution]

Try:

fixed quantity = calculate(distinctcount(ID), filter(ALL(table), Date = Date(2018, 1, 31)))
dynamic quantity = distinctcount(ID)
diff = dynamic quantity - fixed quantity