Forum Discussion
Append a filtered value to another value
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
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 ) )
3 Replies
my fiscal year ends in February, thus another 3 months
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 ) )
