Forum Discussion

vivica19's avatar
vivica19
Frequent Visitor
2 years ago

Price Increase Decrease Flag earliest date

Hi good day! 

 

Yesterday (with help) I created this formula for a Price Increase Flag for my report
The issue is that the _MinDate that I expect as a result is the one before the first false result, as marked in the pic. 

 

The condition will be IF for the latest date the ANY_PRICE_INC = TRUE, THEN return the ASP for the Min date before the first FALSE. 

Not the last or first TRUE in the column, but the first of the consecutive TRUE values only when the most recent is TRUE.

I posted a sample of the data in the table below, the column flag has the result I expect. 

Is there a way to do this? 

thanks in advance! 

 

 

 

Price INC flag =
       VAR __CurrentDate = MAX( 'Sales - Market Level'[DATE] )
    VAR __MaxDate = MAXX(ALLSELECTED('Sales - Market Level'[DATE]),'Sales - Market Level'[DATE])
    VAR __IsTrue = SELECTCOLUMNS(FILTER( ALLSELECTED( 'Sales - Market Level' ), [ANY_PRICE_INC] = TRUE() && [DATE] = __MaxDate ), "__AnyPriceInc", [ANY_PRICE_INC] )
    VAR __MinDate = MINX( FILTER( ALLSELECTED( 'Sales - Market Level'), [ANY_PRICE_INC] = TRUE() ), [DATE] )
    VAR __MinDateRate = MAXX( FILTER( ALLSELECTED( 'Sales - Market Level' ), [ANY_PRICE_INC] = TRUE() && [DATE] = __MinDate ), [ASP] )
    VAR __Result = IF( __IsTrue && __CurrentDate = __MinDate, __MinDateRate, BLANK() )
RETURN
    __Result
 
DATEASPANY_PRICE_DECANY_PRICE_INCFlag
8/26/2023 0:00$49.23FALSETRUE 
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.77FALSETRUE 
3/11/2023 0:00$39.09FALSETRUE 
3/4/2023 0:00$38.52FALSEFALSE 
1/28/2023 0:00$38.87FALSEFALSE 
Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Your increase/decrease columns don't seem to match your data. Please clarify.

     

     

    • vivica19's avatar
      vivica19
      Frequent Visitor
      2 years ago

      The ANY_PRICE_INC and ANY_PRICE_DEC columns are based on other calculations of price variation (short/long time). they mean that the ASP changed at any moment over a period of time. I only included the columns I need for the flag. I included the ASP because I need it in the result, but if I can get the date it will also work for me.