Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cris1196
Helper I
Helper I

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):

 
 
 
Thanks!
 

 

0 REPLIES 0

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors