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 mix values for all the month like .
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 my 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 .
Thank you , Please help me .
