Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 ) )
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 ) )
my fiscal year ends in February, thus another 3 months
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |