Dax Distinct count filtering current vs last reported data correctly

[DKMuller]

Hi 

I hope someone can help me move forward on this issue, I have in powerpivot created a measure for previous reported data. The measure I have created show correct grand total, but do not specify correctly previous reported data (difference in bank account between March 2 and March 4) , I think due to pivot filtering (March 4) and current reported data have less row specification than previous reported data. 

For March 4 measure Bank Acc Prev Date need to specify 173 Bank accounts, but only specify 155, something is wrong in my measure for Bank Acc Prev Date. 

'=CALCULATE(DISTINCTCOUNT('BankBalanceData'[Bank account]); FILTER(ALLEXCEPT(BankBalanceData; BankBalanceData[Bank]; BankAcc); 'BankBalanceData'[Date] = (Max('BankBalanceData'[Prev Date]))))

Same issue for Bank Account and for Bank Account Balance, issue only explained for Bank Account issue.

Bank Acc: (measure)

=DISTINCTCOUNT('BankBalanceData'[Bank account])

Simple count per pivot filter of Bank accounts.

Bank Acc Prev Date: (measure)

'=CALCULATE(DISTINCTCOUNT('BankBalanceData'[Bank account]); FILTER(ALLEXCEPT('BankBalanceData';'BankBalanceData'[Bank];'BankBalanceData'[Bank account];'BankBalanceData'[Bank account name]; BankAcc; 'BankBalanceData'[EntityGroup];CognosEntity[EntityGroup]); 'BankBalanceData'[Date] = (Max('BankBalanceData'[Prev Date]))))

=CALCULATE(DISTINCTCOUNT('BankBalanceData'[Bank account]); FILTER(ALLEXCEPT(BankBalanceData; BankBalanceData[Bank]; BankAcc); 'BankBalanceData'[Date] = (Max('BankBalanceData'[Prev Date]))))

Two calculation giving same result, but result does not show correct specifications.

Trying to calculate difference between current Bank account reporting and same reported on date before current, where current = pivot filter.

Bank accounts specification (row spec) does not match previous reported Bank accounts, but only current Bank accounts = spec is wrong. Grand total is correct.

Prev Date: (calculated column)

=CALCULATE((sum([Balance DKK])); (FILTER(ALLEXCEPT('BankBalanceData';BankBalanceData[Bank account]);[Date]=EARLIER([Prev Date]))))

Bank data is not updated for every date, this calculation support to find last repported data.

[v-yulgu-msft]

Hi @DKMuller,

As this problem is related to PowerPivot, you'd better post thread on a corresponding forum to get more professional advice. Besides, to better illustrate your requirement, please post sample data.

Regards,

Yuliana Gu