Append a filtered value to another value

[lmundia]

I have a variance value generated from past months and I need to append it to future months, how can I do it in DAX, when I do CALCULATE(future_month_value+variance_from_past_months) my variance is set to 0 because the future months dont have a variance. I have tied to assign the variance to value measure measure = value(variance_from_past_months) that didnt help. 

Please help

Thank you

[v-sihou-msft · Accepted solution]

@lmundia

In this scenario, I assume you want to assign the last non empty variance to your future months. You need to get the max date for all non empty variance entries. Then get the last non empty variance. It can be like: 

=
VAR MaxDate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALL ( Table ), Table[Variance] <> BLANK () )
    )
RETURN
    CALCULATE (
        SUM ( Table[Variance] ),
        FILTER ( ALL ( Table ), Table[Date] = MaxDate )
    )

[Abduvali]

Hi @lmundia,

When you say a future month, how many months do you mean???

Regards

Abduvali

[lmundia]

my fiscal year ends in February, thus another 3 months