distinctcount from past period

[Supaplex]

Hi!

I have an issue with distinctcount function:

We have sales table with actual data in January 2017 and January 2018.

For example:

date	point_key	amount
01.01.2017	111	200
01.01.2017	333	100
01.01.2017	222	300
01.01.2017	333	400
01.01.2018	111	100
01.01.2018	444	400
01.01.2018	555	200
01.01.2018	111	300

I can take DISTINCTCOUNT of point_keys if I select filter (January 2018) in month = CALCULATE(DISTINCTCOUNT([point_key])) = 3

I can take DISTINCTCOUNT of point_keys in same month of prior year = CALCULATE(DISTINCTCOUNT([point_key]);DATEADD('Calendar'[Date];-1;YEAR)) = 3

The matter is how to get increment and decrement in unique point_keys between CY and PY?

in our case we can see that point_key 555 has appeared in January 2018 and point_key 222 has disappeared from January 2017.

Well, can I put increment and decrement into different calculated measures?

I've tried for increment SUMX(ADDCOLUMNS(mytable;"stream";IF(DISTINCTCOUNT([point_key]) - CALCULATE(DISTINCTCOUNT([point_key]);DATEADD('Calendar'[Date];-1;YEAR))=1;1;BLANK()));[stream])

but it sums rows in fact_table 😞

[v-yuta-msft]

Hi Supaplex,

For further test, could you please provide more details about CY , PY? In addtion,  are [stream], [increment] and [decrement] the measures you have created? Could you please clarify these measures and your expected result?

Regards,

Jimmy Tao

[Supaplex]

Hi!

I have an issue with distinctcount function:

We have sales table with actual data in January 2017 and January 2018.

For example:

date	point_key	amount
01.01.2017	111	200
01.01.2017	333	100
01.01.2017	222	300
01.01.2017	333	400
01.01.2018	111	100
01.01.2018	444	400
01.01.2018	555	200
01.01.2018	111	300

I can take DISTINCTCOUNT of point_keys if I select filter (January 2018) in month = CALCULATE(DISTINCTCOUNT([point_key])) = 3

I can take DISTINCTCOUNT of point_keys in same month of prior year = CALCULATE(DISTINCTCOUNT([point_key]);DATEADD('Calendar'[Date];-1;YEAR)) = 3

The matter is how to get increment and decrement in unique point_keys between CY and PY?

in our case we can see that point_key 555 has appeared in January 2018 and point_key 222 has disappeared from January 2017.

Well, can I put increment and decrement into different calculated measures?

I've tried for increment SUMX(ADDCOLUMNS(mytable;"stream";IF(DISTINCTCOUNT([point_key]) - CALCULATE(DISTINCTCOUNT([point_key]);DATEADD('Calendar'[Date];-1;YEAR))=1;1;BLANK()));[stream])

but it sums rows in fact_table 😞