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, I am trying to identify an increased score of a topic over the past two or three weeks. I get the data every 7 days. I only am interested in seeing the increase relative to the most recent week. Below, the right two columns are the two I want to create (and do not exist), but don't know how to code an IF/Lookup formula.
Solved! Go to Solution.
you can try this
Column =
VAR last=maxx(FILTER('Table','Table'[Topic]=EARLIER('Table'[Topic])&&'Table'[Date]=EARLIER('Table'[Date])-7),'Table'[Score])
return if('Table'[Date]=max('Table'[Date])&&'Table'[Score]>last,"Yes","No")
Column 2 =
VAR last=maxx(FILTER('Table','Table'[Topic]=EARLIER('Table'[Topic])&&'Table'[Date]=EARLIER('Table'[Date])-7),'Table'[Score])
VAR last2=maxx(FILTER('Table','Table'[Topic]=EARLIER('Table'[Topic])&&'Table'[Date]=EARLIER('Table'[Date])-14),'Table'[Score])
return if('Table'[Date]=max('Table'[Date])&&'Table'[Score]>last&&last>last2,"Yes","No")
Proud to be a Super User!
not quite clear about the logic.
1. Why the IT for incresaed in 3 consecutive weeks is not "Yes"? It looks like 90>85>70
2. For incresed in consecutive weeks column ,why IT on 2021/3/27 is No? it looks like 85>70. does this column need to compare the value with past two weeks? if so, you only have three weeks value, can't get the result for last column.
Proud to be a Super User!
Thank you for pointing out my flawed data. I have updated it now and should follow the logic. See answers/clarification below.
1. IT is now 90>85<92
2. I only want to return Yes if the topic increased in consecutive weeks AND the latest week is the week that it increased. Since I just changed the data above. Look at Cyber as an example. Cyber increased from 65 (3/20/21) to 80 (3/27/21), but there is a “No” in the column for 3/27/21 because 3/27/21 is not the latest week. Hope this helps clarify.
you can try this
Column =
VAR last=maxx(FILTER('Table','Table'[Topic]=EARLIER('Table'[Topic])&&'Table'[Date]=EARLIER('Table'[Date])-7),'Table'[Score])
return if('Table'[Date]=max('Table'[Date])&&'Table'[Score]>last,"Yes","No")
Column 2 =
VAR last=maxx(FILTER('Table','Table'[Topic]=EARLIER('Table'[Topic])&&'Table'[Date]=EARLIER('Table'[Date])-7),'Table'[Score])
VAR last2=maxx(FILTER('Table','Table'[Topic]=EARLIER('Table'[Topic])&&'Table'[Date]=EARLIER('Table'[Date])-14),'Table'[Score])
return if('Table'[Date]=max('Table'[Date])&&'Table'[Score]>last&&last>last2,"Yes","No")
Proud to be a Super User!
Thank you, this worked!
Hi @Ashish_Mathur, thanks for the code. However, I am getting the following error: "A table of multiple values was supplied where a single value was expected."
Increased in consecutive weeks = if(CALCULATE(MAX(Comprehensive[Date]),FILTER(Comprehensive,Comprehensive[Topic]=EARLIER(Comprehensive[Topic])))=Comprehensive[Date],Comprehensive[Score]>LOOKUPVALUE(Comprehensive[Score],Comprehensive[Topic],Comprehensive[Topic],Comprehensive[Date],CALCULATE(MAX(Comprehensive[Date]),FILTER(Comprehensive,Comprehensive[Topic]=EARLIER(Comprehensive[Topic])&&Comprehensive[Date]<EARLIER(Comprehensive[Date])))),FALSE())
Hi,
Could be some bracketing problem. Please recheck.
Hi,
Write this as a calculated column formula
Increased in consecutive weeks = if(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Topic]=EARLIER(Data[Topic])))=Data[Date],Data[Score]>LOOKUPVALUE(Data[Score],Data[Topic],Data[Topic],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Topic]=EARLIER(Data[Topic])&&Data[Date]<EARLIER(Data[Date])))),FALSE())
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |