- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Create a measure: cumulative sum of first order by customer and month, friendly with new filters
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):
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
02-10-2024 06:39 AM | |||
07-07-2024 08:25 AM | |||
04-02-2024 02:03 PM | |||
09-06-2023 02:17 AM | |||
04-04-2024 11:58 AM |
User | Count |
---|---|
21 | |
17 | |
12 | |
6 | |
5 |
User | Count |
---|---|
29 | |
22 | |
20 | |
13 | |
10 |