Calculating Rolling Sum But Can't Remove Duplicates

[Anonymous]

Hello,

I've written this function meant to calculate the total number of users on a rolling basis over the last 7 days. The only issue I have now is that if someone used it on more than one day, it counts the person more than once, which is inflating the count. I've been working on it the last few hours and nothing I've tried has helped. Here's the function as of now:

Thanks so much for your help!

[camargos88]

Hi @Anonymous ,

Can you provide some data sample and the expected output ?

Ricardo

[Anonymous]

 For April 17, the count should be 109 but it was 216 since users who used it on more than one day are counted more than once. Thanks for the help!

[camargos88]

@Anonymous ,

Do you have sample data of this ? I can try to reproduce it.

Ricardo

[Anonymous]

Hi there,

The data contains sensitive information, which I wouldn't be allowed to share unfortunately. I'm happy to test any and as many changes to the DAX function as possible. I'm sorry for these restrictions and appreciate your help.

[Anonymous · Accepted solution]

// First of all, you should not store all data
// in one table. That's not only Bad Practice...
// it's also dangerous and leads to subtle bugs
// you'll not be able to spot. Please ALWAYS
// use correct star- or snowflake-schemas.
// Second, you are counting same users many times
// because you're iterating dates and for each
// such date you are doing a distinct count
// of the users... but on the currently iterated
// day. What you should do instead is you should 
// select the whole period at once and then
// do a distinct count of users.
// The measure should most likely be only shown
// when only one day is visible in the current
// context, therefore it must check for the
// number of days visible.
// The most important table in all models is
// a date table (Calendar). So, please make sure
// you're doing it RIGHT.
// If you have a CORRECT model, the calculation
// proceeds as follows:

[7D Rolling Count] =
var __oneDayVisible = hasonevalue( Calendar[Date] )
var __lastVisibleDay = max( Calendar[Date] )
var __periodToCountOver =
	datesinperiod(
		Calendar[Date],
		__lastVisibleDay,
		-7, day
	)
var __result =
	calculate(
		distinctcount( FactTable[UserId] ),
		__periodToCountOver
	)
return
	if( __oneDayVisible, __result )

If you want to learn a bit about CORRECT MODELS, you can try these:

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

Creating DAX on INCORRECT or MESSY models is not only difficult. It's also error-prone. Good model = simple, fast DAX. Bad model = complex, slow DAX. Easy as that.

Best

D

[Anonymous]

This solution looks to have solved my problem and I really appreciate your assistance on this issue. Have a great day @Anonymous!

[Anonymous]

Hi there.

@Anonymous, would you mind marking the answer to your question as THE answer, please? It's the "Accept as Solution" button under the solution... It'll help others.

Also, if you can, please add kudos to the relevant posts. This, in turn, helps the authors of the answers.

Thanks.

Best
D

[camargos88]

@Anonymous ,

Check if it's that what you are looking for: Download PBIX 

If you consider it as a solution, please mark as solution and kudos.

Ricardo

[Anonymous]

Hi Ricardo,

I just got back to work this morning and tested out your solution. It gets me closer to the actual numbers, but not exactly there, which I am investigating why now. I really appreciate the assistance, regardless.