Complex IF Formula Help

[shaebert]

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. 

[ryan_mayu]

@shaebert 

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.

[shaebert]

@ryan_mayu 

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. 

[ryan_mayu · Accepted solution]

@shaebert 

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

[shaebert]

Thank you, this worked!

[shaebert]

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

[Ashish_Mathur]

Hi,

Could be some bracketing problem.  Please recheck.

[Ashish_Mathur]

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