Forum Discussion

vivica19's avatar
vivica19
Frequent Visitor
2 years ago
Solved

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 mos...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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.

vivica19's avatar
vivica19
Frequent Visitor
2 years ago

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 

DATEASPANY_PRICE_DECANY_PRICE_INCFirst Time Price Changed FLAG
8/26/2023 0:00$49.23FALSETRUEOnly if this value is TRUE
8/19/2023 0:00$49.65FALSETRUE 
8/12/2023 0:00$47.09FALSETRUE 
8/5/2023 0:00$48.68FALSETRUE 
6/24/2023 0:00$40.66FALSETRUE 
6/17/2023 0:00$46.03FALSETRUE 
6/10/2023 0:00$47.61FALSETRUE 
6/3/2023 0:00$48.60FALSETRUE 
5/27/2023 0:00$48.19FALSETRUE 
5/20/2023 0:00$45.53FALSETRUE 
5/13/2023 0:00$48.08FALSETRUE 
5/6/2023 0:00$45.81FALSETRUE$45.81
3/25/2023 0:00$39.15FALSEFALSE 
3/18/2023 0:00$38.77FALSEFALSE 
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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

     

    • vivica19's avatar
      vivica19
      Frequent Visitor
      2 years ago

      it worked perfectly well...!!! thanks sooooo much 🙂