Forum Discussion
Price Increase/Decrease Flag
Hi,
I am trying to create a flag for price inc/dec where if the latest date is true in the ANY_PRICE_INC column it takes the date of the first Consecutive row.
In this case for example the most recent date 8/26/2023 is TRUE, so it will flag only the first TRUE date of the consecutive rows that will be 5/6/2023. This flag will work only if the most recent date is TRUE.
Any suggestions on how to accomplish this? thanks in advance!
vivica19 I feel like I am still missing some critical information, but you could do this potentially:
Measure = VAR __CurrentDate = MAX( 'Table'[DATE] ) VAR __MaxDate = MAXX(ALLSELECTED('Table'), [DATE]) VAR __IsTrue = SELECTCOLUMNS(FILTER( ALLSELECTED( 'Table' ), [ANY_PRICE_INC] = TRUE() && [DATE] = __MaxDate ), "__AnyPriceInc", [ANY_PRICE_INC] ) VAR __MinDate = MINX( FILTER( ALLSELECTED('Table'), [ANY_PRICE_INC] = TRUE() ), [DATE] ) VAR __MinDateRate = MAXX( FILTER( ALLSELECTED( 'Table' ), [ANY_PRICE_INC] = TRUE() && [DATE] = __MinDate ), [ASP] ) VAR __Result = IF( __IsTrue && __CurrentDate = __MinDate, __MinDateRate, BLANK() ) RETURN __Result
4 Replies
vivica19 Sorry, what is the expected output? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.the condition is IF the latest date value is TRUE, THEN return the first date that the price changed or the ASP value for that date. I need to create a flag for both Any price decrease and increase columns.
See the example in this table
DATE ASP ANY_PRICE_DEC ANY_PRICE_INC First Time Price Changed FLAG 8/26/2023 0:00 $49.23 FALSE TRUE Only if this value is TRUE 8/19/2023 0:00 $49.65 FALSE TRUE 8/12/2023 0:00 $47.09 FALSE TRUE 8/5/2023 0:00 $48.68 FALSE TRUE 6/24/2023 0:00 $40.66 FALSE TRUE 6/17/2023 0:00 $46.03 FALSE TRUE 6/10/2023 0:00 $47.61 FALSE TRUE 6/3/2023 0:00 $48.60 FALSE TRUE 5/27/2023 0:00 $48.19 FALSE TRUE 5/20/2023 0:00 $45.53 FALSE TRUE 5/13/2023 0:00 $48.08 FALSE TRUE 5/6/2023 0:00 $45.81 FALSE TRUE $45.81 3/25/2023 0:00 $39.15 FALSE FALSE 3/18/2023 0:00 $38.77 FALSE FALSE vivica19 I feel like I am still missing some critical information, but you could do this potentially:
Measure = VAR __CurrentDate = MAX( 'Table'[DATE] ) VAR __MaxDate = MAXX(ALLSELECTED('Table'), [DATE]) VAR __IsTrue = SELECTCOLUMNS(FILTER( ALLSELECTED( 'Table' ), [ANY_PRICE_INC] = TRUE() && [DATE] = __MaxDate ), "__AnyPriceInc", [ANY_PRICE_INC] ) VAR __MinDate = MINX( FILTER( ALLSELECTED('Table'), [ANY_PRICE_INC] = TRUE() ), [DATE] ) VAR __MinDateRate = MAXX( FILTER( ALLSELECTED( 'Table' ), [ANY_PRICE_INC] = TRUE() && [DATE] = __MinDate ), [ASP] ) VAR __Result = IF( __IsTrue && __CurrentDate = __MinDate, __MinDateRate, BLANK() ) RETURN __Result
