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 🙂