Exclude current month values via calculated column

[Anonymous]

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.

[v-xicai · Accepted solution]

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.

[amitchandak]

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

[Anonymous]

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.

[Anonymous]

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.