Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, good morning/afternoon/evening.
I leave attached first of all the excel that I am using as a database:
Link
So what I'm trying to achieve is:
1- in a variable, obtein all the values (in this case it would be the IDs) that are in week N
2-in another variable, get all the values that are in week N+1, MIN(Day)
3-in another variable, get all the values that are in week N+1, MIN(Day)+1
4-etc
That is, a variable for each day of each week and then I compare how many values of one variable are found in another.
So what I did was:
First I create two columns in Table 1:
Weeks = CONCATENATE(YEAR(TODAY()),
IF(WEEKNUM(TODAY())<10, CONCATENATE(0, WEEKNUM(TODAY())),
WEEKNUM(TODAY())))
Days_ = DAY(DATETIME)
Then I create the following measure:
Measure =
var weeks_ = CONCATENATE(YEAR(TODAY()), IF(WEEKNUM(TODAY())<10, CONCATENATE(0, WEEKNUM(TODAY())), WEEKNUM(TODAY())))
//with this I get the year/month
var W1 = CALCULATETABLE(VALUES(Table1[ID]), Table1[Weeks] =weeks_-2)
//With this, I try to obtain the IDs that are in the week where we are-2
var W2A = CALCULATETABLE(VALUES(Table1[ID]), Table1[Weeks] =cohort-1,
Table1[Days_] = MIN(TABLE1[Days_]))
//I try to obtain, from the next week of the first variable, all the values that are in the first day of it
var W2B = CALCULATETABLE(VALUES(Table1[ID]), Table1[Weeks]=cohort-1,
Table1[Days_] = MIN(TABLE1[Days_])+1)
//Exactly the same, but for the second day
var W2C = ... would be the same but with TABLE1[Days_])+2 and so on until the last day of the week
return
IF(SELECTEDVALUE(TABLE1[Weeks])=cohort-1 && SELECTEDVALUE(TABLE1[Days_])=1, COUNTROWS(INTERSECT(W2A, W1)),
//Here I begin to compare, how many values reappear between the weeks, biased by day.
IF(SELECTEDVALUE(TABLE1[Weeks])=cohort-1 && SELECTEDVALUE(TABLE1[Days_])=2, COUNTROWS(INTERSECT(W2B, 21)),
.
.
.
etc,
0))
My two problems are:
First, the final result brings me only zero, despite the fact that as I am going to show you below, the variables do bring results and if I see in Excel, there really are values that would be repeated and should appear in the intersect:
The other problem is that if I do in a separate measure, the countrows of a single variable, that is, for example countrows(W21), I get results but these change, even though I am explicitly telling them to take the value of a certain week and day.
Any help that solves the problem and/or tells me where I am going wrong with my logic will be appreciated 😀
@CLQ , if you are trying to work on Week On week calculation then refer my Rank calculation
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |