I am looking for a DAX to use in calc column. (Not a measure, because I will use it for further calculations)It should calculate Distinctcount of stores in last 4 weeks. Here is how it should work (last column): CountryEanIDCategoryYYYY_MM_WWeek idStoreTurnover$Distinctcount of stores in last 4 weeksSE3111ORAL CARE2018_01_197NOR1 DE3111ORAL CARE2018_01_197HUM1 SE3111ORAL CARE2018_01_299KOS4 SE3111ORAL CARE2018_01_299KON3 SE3111ORAL CARE2018_01_4101ARJ43SE3111ORAL CARE2018_03_9107ARJ41SE3111ORAL CARE2018_04_14114ARJ41SE3111ORAL CARE2018_04_17117ROM42SE3111ORAL CARE2018_05_20121KOS41SE3111ORAL CARE2018_05_21122HAS34SE3111ORAL CARE2018_05_21122FAL34SE3111ORAL CARE2018_05_21122ROM44SE3111ORAL CARE2018_06_23125FAL23SE3111ORAL CARE2018_06_24126FAL81DE3111ORAL CARE2018_06_24126KOB11DE2014ORAL CARE2018_01_197ALB5 DE2014ORAL CARE2018_01_197ARH1 DE2014ORAL CARE2018_01_197SKO1 DE2014ORAL CARE2018_01_299KVI2 SE2014ORAL CARE2018_01_3100NYH71DE2014ORAL CARE2018_01_3100SUP35SE2014ORAL CARE2018_01_4101BRA32DE2014ORAL CARE2018_01_4101HUM14DE2014ORAL CARE2018_01_4101OST14DE2014ORAL CARE2018_02_7105AAL11DE2014ORAL CARE2018_02_8106ARH12DE2014ORAL CARE2018_03_11110NDR61 I would like this to be calculated by Country, Category and EanID.For example:For Country = SE, for EanID = 3111, for Category = ORAL CAREin Week id = 101, I have a 3 distinct Stores selling in last four weeks: ARJ, KON and KOS. The last four weeks are: 101, 100, 99, 98. So Distinctcount of stores in last 4 weeks = 3Please note Week id is non continuous. Thanks in advance

Anonymous

Not applicable

8 years ago

Solved

Distinctcount by groups in calc column

I am looking for a DAX to use in calc column. (Not a measure, because I will use it for further calculations)

It should calculate Distinctcount of stores in last 4 weeks. Here is how it should work (last column):

Country	EanID	Category	YYYY_MM_W	Week id	Store	Turnover$	Distinctcount of stores in last 4 weeks
SE	3111	ORAL CARE	2018_01_1	97	NOR	1
DE	3111	ORAL CARE	2018_01_1	97	HUM	1
SE	3111	ORAL CARE	2018_01_2	99	KOS	4
SE	3111	ORAL CARE	2018_01_2	99	KON	3
SE	3111	ORAL CARE	2018_01_4	101	ARJ	4	3
SE	3111	ORAL CARE	2018_03_9	107	ARJ	4	1
SE	3111	ORAL CARE	2018_04_14	114	ARJ	4	1
SE	3111	ORAL CARE	2018_04_17	117	ROM	4	2
SE	3111	ORAL CARE	2018_05_20	121	KOS	4	1
SE	3111	ORAL CARE	2018_05_21	122	HAS	3	4
SE	3111	ORAL CARE	2018_05_21	122	FAL	3	4
SE	3111	ORAL CARE	2018_05_21	122	ROM	4	4
SE	3111	ORAL CARE	2018_06_23	125	FAL	2	3
SE	3111	ORAL CARE	2018_06_24	126	FAL	8	1
DE	3111	ORAL CARE	2018_06_24	126	KOB	1	1
DE	2014	ORAL CARE	2018_01_1	97	ALB	5
DE	2014	ORAL CARE	2018_01_1	97	ARH	1
DE	2014	ORAL CARE	2018_01_1	97	SKO	1
DE	2014	ORAL CARE	2018_01_2	99	KVI	2
SE	2014	ORAL CARE	2018_01_3	100	NYH	7	1
DE	2014	ORAL CARE	2018_01_3	100	SUP	3	5
SE	2014	ORAL CARE	2018_01_4	101	BRA	3	2
DE	2014	ORAL CARE	2018_01_4	101	HUM	1	4
DE	2014	ORAL CARE	2018_01_4	101	OST	1	4
DE	2014	ORAL CARE	2018_02_7	105	AAL	1	1
DE	2014	ORAL CARE	2018_02_8	106	ARH	1	2
DE	2014	ORAL CARE	2018_03_11	110	NDR	6	1

I would like this to be calculated by Country, Category and EanID.

For example:

For Country = SE, for EanID = 3111, for Category = ORAL CARE

in Week id = 101, I have a 3 distinct Stores selling in last four weeks: ARJ, KON and KOS.

The last four weeks are: 101, 100, 99, 98. So Distinctcount of stores in last 4 weeks = 3

Please note Week id is non continuous.

Thanks in advance

Need Help

Zubair_Muhammad

8 years ago

Anonymous

May be something like this

Column =
CALCULATE (
    DISTINCTCOUNT ( Table1[Store] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Country], Table1[EanID], Table1[Category] ),
        Table1[Week id] <= EARLIER ( Table1[Week id] )
            && Table1[Week id]
                >= EARLIER ( Table1[Week id] ) - 3
    )
)

Forum Discussion

Distinctcount by groups in calc column

4 Replies

Related content

Grouping rows by Column and then updating Column

Create columns group

Grouping Columns

Fabric & Azure Data User Group Program Requirements & Benefits

Matrix column not grouped