dax

[Anonymous]

this is count cal when -ve retailing is included.

p3m =

var a= MAX('PSR'[Month])-2

var b =CALCULATE(DISTINCTCOUNT('PSR'[Customer_Code]),'PSR'[Month]>=a && 'PSR'[Month]<=MAX('PSR'[Month]))

return

b
this is the formula for the above visual. It calculates distinct count of customers where sept=sept+aug+july, aug= aug+july+june and so on.
in excel pivot it count is different it is 24225 for sept and that is the count i want.
https://drive.google.com/drive/folders/1i8pY-97Y7r7alnWXvQeXEjqarzGAnsdS?usp=drive_link data link.

@Ahmedx 

[Uzi2019]

HI @Anonymous 
Please do the following steps:
Create new measure:

Cust>0 = CALCULATE(COUNTROWS(Customer),FILTER(Customer,Customer[Retailing]>0))

Create new measure 

Cummuative =

Var N= MAX(Customer[Month])-2

Var A=CALCULATE(Customer[Cust>0],'Customer'[Month]>=N && 'Customer'[Month]<=MAX('Customer'[Month]))

Return A

I hope I answered your question.

[Anonymous]

works 100% just 1 condition that i need is sum(retailing) and then distinctcount of customers. i got this result earlier also it is not wrong just after sum of retailing is applied then the count comes out to be different. ALso im not able to add this "sum of retailing" in the above measure

[Uzi2019]

Hi @Anonymous 
Can you give me example by smapled data how you want Sum (retailing) to be added??
On which condition ?
or is it different measure or what?
Initially you want the count(cust) whose retailing >0 right. then you want 3 months count together.

Please mention the condition properly

[Anonymous]

=COUNTIF(E10:E24590,">0")

[Anonymous]

that is the distinct count of all months and in the provided dataset.the data is only of 7,8,9th month so total distinct count was 24225. and this should be in the month value 9

[Ahmedx]

can you share file with pivot table

[Anonymous]

https://drive.google.com/drive/folders/1i8pY-97Y7r7alnWXvQeXEjqarzGAnsdS?usp=sharing

[Ahmedx]

why is this range not included in your formula?

[Anonymous]

oh my bad you can include everything

[Ahmedx]

Measure 2 = 
var a= MAX('PSR'[Month])-2
var b =CALCULATE(DISTINCTCOUNT('PSR'[Customer_Code]),'PSR'[Month]>=a && 'PSR'[Month]<=MAX('PSR'[Month])&& 'PSR'[sale]>0)
return
b

[Anonymous]

this measure is fine but not 100% correct. after taking sum of retailing then the distinct count should be applied.

[Ahmedx]

I don’t understand, please explain what else is needed

[Anonymous]

the retailing value are + and - both .......in this case if some retailing is done lets say 20 then naturally the count should be 1 but the product he recieved were damaged so he returned those products i.e -20. technically that customer had never ordered. +20-20= 0 . so it should not be counted. but in this case in power bi when we exclude -ve retailing at that time only -20 is excluded and +20 remains there. so still that customers count is considered. and in excel that is not the case. In excel it is taking the sum of retailing and then the count is being taken. i want the excel value.

[Ahmedx]

Please check attached pbix for more details.

[Ahmedx]

Hello! did you check my file

[Anonymous]

@Ahmedx ?

[Ahmedx]

Explain how you got this value (24225) in Excel