Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Exclude current month values via calculated column

Hello, I am trying to exclude values with the date = the last nonblank date & with the value_type = 'actuals'. I make this distinction because I am graphing Forecast vs Actuals , & forecast values go out to end of 2021, and actuals are actualized months that appear in our db the next mid-month for the previous month. I'm having an issue graphing net gains where for the most current month with no actuals values populated yet, it is showing a complete negative net gain, so I'd like to exclude this month of data.

To get the last month with nonblank values (accurate): 

CALCULATE(LASTNONBLANKVALUE('Main'[date_column],MAX('Main'[date_column])),'Main'[value_type]="actuals")

But now to exclude the actuals values, this below does not work:

IF(AND('Main'[value_type]="actuals",'Main'[date_column] = CALCULATE(LASTNONBLANKVALUE('Main'[date_column],MAX('Main'[date_column])),'Main'[value_type]="actuals")

),"Delete","")

 

THank you in advance.

 

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    You may create calculated column like DAX below.

     

    Column1 =
VAR _MaxDate =
    CALCULATE (
        MAX ( Main[date] ),
        ALLEXCEPT ( Main, Main[product_name], Main[value_type] )
    )
RETURN
    IF ( Main[value_type] = "actuals" && Main[date] = _MaxDate, "Delete", BLANK () )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    To add more information, when I use the second formula above for the calculated column, it incorrectly excludes all actuals values. When I'd like to just exclude only that month's values for actuals.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Can you share sample data and sample output in table format?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, here is a screenshot to illustrate. For example today on 7/21, I'd like to exclude row 4 from appearing on a Net Gains visualization by filtering out "Delete" in the calculated column. But cannot get the formula correct.

       

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You may create calculated column like DAX below.

     

    Column1 =
VAR _MaxDate =
    CALCULATE (
        MAX ( Main[date] ),
        ALLEXCEPT ( Main, Main[product_name], Main[value_type] )
    )
RETURN
    IF ( Main[value_type] = "actuals" && Main[date] = _MaxDate, "Delete", BLANK () )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.