Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi! I hope you are well. I was developing a Power BI measure and I found a problem:
I want to see variation of clients that enter to an app per week.
I have a table like these in which "Alta_Pedido_Dt" is the date in which the client entered the app, "Cliente" is the unique identifier of that client.
What I expect is this:
The first two registers correspond to the first week and the same client, the other rows correspond to this week (7 different clients). I want to create a measure that does:
Measure = Quantity of different clients that entered the app this week(7) / Quantity of different clients that entered the last week(1) -1 = 6
I ve developed the next measures:
Q_USERS = DISTINCTCOUNT(TABLE1[Cliente])
USERS_VARIATION_PER_WEEK =
VAR _ACTUAL_WEEK = CALCULATE(SUMX(TABLE1,[Q_USERS]),FILTER(Calendar,Calendar[WEEK] = WEEKNUM(max(TABLE1[Alta_Pedido_Dt]),1)))
VAR _LAST_WEEK = CALCULATE(SUMX(TABLE1,[Q_USERS]),FILTER(Calendar,Calendar[WEEK] = (WEEKNUM(max(TABLE1[Alta_Pedido_Dt]),1)-1)))
RETURN
ROUND(DIVIDE(_ACTUAL_WEEK,_LAST_WEEK)-1,1)
But ive got an unsatisfying result:
The value of the measure Q_USERS that i ve created is right for the week 33 and 34. But MEASURE_CREATED(USERS_VARIATION_PER_WEEK) is 3.5, why? There´s something that i am missing.
I wait for your help. Thank u so much!
Solved! Go to Solution.
@Anonymous , In the table use the Week from Date table, also Change measures like
and use the week rank as given below
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
@Anonymous , In the table use the Week from Date table, also Change measures like
and use the week rank as given below
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
85 | |
67 | |
49 |
User | Count |
---|---|
132 | |
113 | |
100 | |
68 | |
67 |