Create a measure: cumulative sum of first order by customer and month, friendly with new filters

cris1196 — Thu, 13 Apr 2023 13:54:53 GMT

Hi all.

I have a table like this:

Row	Date	YearMonth	UserId	StoreId	RankOrder	ProvinceId
1	2022-05-01	202205	1	5	1	5
2	2022-05-02	202205	1	7	2	1
3	2022-05-03	202205	1	5	3	9
98	2022-06-04	202206	1	8	4	2
99	2022-07-05	202207	1	5	5	1

- The "RankOrder" column is a column created that indicates the user's order number (I use the "Date" column. This column is in datetime format, so there will be a unique value per order. In this example, I am putting it in format date to exemplify)

So to create a measure that tells me the cumulative sum of new users per month, I create this measure:

Cumulative New Users =
CALCULATE(
DISTINCTCOUNT('Table'[UserId])
,FILTER(ALLSELECTED('Date_Table'), 'Date_Table'[Date]<=MAX('Date_Table'[Date]) && 'Date_Table'[YearMonth]=SELECTEDVALUE('Date_Table'[YearMonth]))
,FILTER(ALLSELECTED('Table'), 'Table'[RankOrder]=1)
)

But I find two problems with this logic:

1- If I want to add a slicer, for example, the "ProvinceId" column, this would not work, because for example if I select ProvinceId = 1, instead of bringing me the second row, it would not bring me any results. The solution would be to create another RankOrder column grouped by ProvinceId, but it doesn't seem efficient to me to create columns every time you want to add a filter (since you should also create for each possible combination of filters every time you add more)

2- Multiple selection of filters would bring me another result. Example if in the StoreId slicer I select 7 and 8, instead of bringing me row 2, it would not bring me any row. If I create a RankOrder grouped by user, it would return rows 2 and 98 instead of just StoreId = 2

In short, it has a lot of problems and is not friendly if I want to add more filters. So:

Can anyone think of a way to get the same results and be friendly when selecting multiple options in a filter and/or adding new slicers?

I tried with summarize function (since I found similar things on the internet), but without success 😐 I leave a sample data with several random values , works like the example table of this post (the only difference is that the user IDs are in generated text, not numeric):

SAMPLE DATA

Thanks!

topic Create a measure: cumulative sum of first order by customer and month, friendly with new filters in DAX Commands and Tips

Create a measure: cumulative sum of first order by customer and month, friendly with new filters