Forum Discussion
Create a variable measure which does not gets affected by a date slicer
Hi All and v-xiaotang
I want to retrieve the last date target value for each sub-category (like 40 for product X and 28.44 for Product Y), in such a way that it is not affected by slicer. My aim is to plot a chart showing three values on combo chart with sub-cateogory on x-axis
actual = 11.32 (X) and 15.33 (Y)
target = 13.32 (X) and 17.33 (Y)
planned = 40 (X) and 28.44 (Y)
I put a slicer on combo chart in such a way that the actual and target value varies according to the date but the planned value is a line chart and stays constant, no matter whatever date is chosen on a slicer.
| Category | Sub-Cateogory | Date | Target | Actual |
| Financials | Revenue from X Product | 11/1/2022 | 0 | 0 |
| Financials | Revenue from X Product | 11/1/2023 | 13.32 | 11.32 |
| Financials | Revenue from X Product | 11/1/2024 | 26.68 | 0 |
| Financials | Revenue from X Product | 11/1/2025 | 40 | 0 |
| Financials | Revenue from Y Product | 11/1/2022 | 12 | 10 |
| Financials | Revenue from Y Product | 2/1/2023 | 13.34 | 11.34 |
| Financials | Revenue from Y Product | 5/1/2023 | 14.64 | 12.64 |
| Financials | Revenue from Y Product | 8/1/2023 | 15.99 | 13.99 |
| Financials | Revenue from Y Product | 11/1/2023 | 17.33 | 15.33 |
| Financials | Revenue from Y Product | 2/1/2024 | 18.67 | 0 |
| Financials | Revenue from Y Product | 5/1/2024 | 19.99 | 0 |
| Financials | Revenue from Y Product | 8/1/2024 | 21.33 | 0 |
| Financials | Revenue from Y Product | 11/1/2024 | 22.67 | 0 |
| Financials | Revenue from Y Product | 3/1/2025 | 24.42 | 0 |
| Financials | Revenue from Y Product | 6/1/2025 | 25.77 | 0 |
| Financials | Revenue from Y Product | 9/1/2025 | 27.11 | 0 |
| Financials | Revenue from Y Product | 12/1/2025 | 28.44 | 0 |
1 Reply
- amitchandakSuper User
AlpanaSi ,
a measure like
calculate(Sum(planned [planned ]), removefilters('Date'[Date]))
if there is a common date table and plan is there in different table do not join them