Moving average of distinct count of retailers.

[Rajendrabobade]

Hello ,

I need dax of moving average of Distinct count of retailer code.

Example: below retailer code is apr and may (1657 & 1993 Avg =1825)

same for apr+may+june (5617 + 9315 + 16939 = 10636)

i need same calculation for all the month .

Filter = FY year.

Month name	Distinct count of Retailer code	Expected Output (Average)
Apr	5617	5617
May	9353	7485
June	16939	10636

Kindly help me for creating Dax.

Thank you .

[v-shex-msft]

Hi @Rajendrabobade,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Rajendrabobade]

Thank you for your reply , i need distinct retailer count for each month . below is my explantion please help me on below .

Hello ,
In my data i have retailer code column , for calculation of moving average i need distinct count of retailers code so
i am creating new calculated measure for the same like distinct count of retailer code .

When i am writing a dax for moving average of distinct count retailer that time my code is working but code taken distinct count unique() values for all the month like .
example:If april month i have Retailer code -Rts100 and my distinct count is 1 and agin for may month retailr code is same - Rts100 and for this for distinct count is 1 so dax taken this as 1 becose we takn disticnt count ,but i want output is = 1+1 =2 ,please help me .

below is my formula -

Moving Avg Retailercode = divide( CALCULATE(DISTINCTCOUNT(GRINetSalesDetailsBI[Retailer Code]),filter(allselected('Calendar'),'Calendar'[Date] <=max('Calendar'[Date]))) ,
CALCULATE(distinctcount('Calendar'[Month year]),filter(allselected('Calendar'),'Calendar'[Date] <=max('Calendar'[Date])), not(isblank((GRINetSalesDetailsBI[Retailer Code])))))

below is small set of sample data please help me to write dax .

Date                  Retailr code 

01-04-2020 0.00	RTS061120
01-04-2020 0.00	RTS061120
01-04-2020 0.00	RTE371009
01-04-2020 0.00	RT061120
01-04-2020 0.00	RT581135
01-04-2020 0.00	RT062480
01-04-2020 0.00	RT581125
01-04-2020 0.00	RT061120
01-04-2020 0.00	RT062480
01-04-2020 0.00	RT061172
01-04-2020 0.00	RT062620
01-05-2020 0.00	RT062620
01-05-2020 0.00	RT062620

I want unique reatiler count of each month for moving avergae in my formula retrailr count is unique but whn i write dax that become a taken all month values unique count which is not corrcet we need each month disticn count values to be show .

Below is output
Example: below retailer code distinct count of apr and may (1657 & 1993 Avg =1825)
same for apr+may+june (5617 + 9315 + 16939 = 10636)
i need same calculation for all the month .

[v-shex-msft]

Hi @Rajendrabobade,

So you mean you want to get a cumulative count of code based on two aggregate(distinct count and average) functions, right? If that is the case, you can try the following measure formulas:

Rolling DC=
AVERAGEX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            [Date],
            "DC Code", COUNTROWS ( VALUES ( 'Table'[Retailr code] ) )
        ),
        [Date] <= MAX ( 'Table'[Date] )
    ),
    [DC Code]
)

Regards,

Xiaoxin Sheng

[Rajendrabobade]

Thank you for reply .

issue is resolved  for resolution we concatinate month and retailer code and put same measure into my formula .so we get automatically Unique count of each month .

[v-shex-msft]

Hi @Rajendrabobade,

Thanks for share your resolve of this scenario, it should be a choose for other users who faced the similar requirements.

Regards,

Xiaoxin Sheng