Forum Discussion

CLQ's avatar
CLQ
Helper I
3 years ago

Problem when comparing the obtained values ​​between two calculatedtables

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 😀




1 Reply