Forum Discussion
Visualize costs dynamically
Anonymous,
I have a way.
First sort [Period] column in ascending order, then, add a [index] column in Edit Queries.Second, create measures:
Last12 = VAR x = CALCULATE( MAX(Sheet1[Index]), ALL(Sheet1) ) RETURN CALCULATE( MAX(Sheet1[Costs]), FILTER( Sheet1, [Index] <= x && [Index] >= x-12 )) Last12 - Last 24 = VAR x = CALCULATE( MAX(Sheet1[Index]), ALL(Sheet1) ) RETURN CALCULATE( MAX(Sheet1[Costs]), FILTER( Sheet1, [Index] <= x-12 && [Index] >= x-24 ))As your data source's rows increase, the [Index] column will automatically increase, so the result is still right.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous,
I have a way.
First sort [Period] column in ascending order, then, add a [index] column in Edit Queries.
Second, create measures:
Last12 =
VAR x =
CALCULATE(
MAX(Sheet1[Index]),
ALL(Sheet1)
)
RETURN
CALCULATE(
MAX(Sheet1[Costs]),
FILTER(
Sheet1,
[Index] <= x && [Index] >= x-12
))
Last12 - Last 24 =
VAR x =
CALCULATE(
MAX(Sheet1[Index]),
ALL(Sheet1)
)
RETURN
CALCULATE(
MAX(Sheet1[Costs]),
FILTER(
Sheet1,
[Index] <= x-12 && [Index] >= x-24
))As your data source's rows increase, the [Index] column will automatically increase, so the result is still right.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lionel-msft , that works perfect! Thanks! One more question: Now I want to visualize the difference between these two graphs in a column chart. I did this by simply creating a measure: Difference = [Last12] - [Last12 - Last 24]. When I create the column chart and only select the month from date hierarchy, I have a column chart shown in the picture. Even though, it finds the correct answer, I would like to show the difference bar under the corresponding months from the line charts, so that when I add new month to the model, then bar chart shifts together with line charts one month forward. Can I get help regarding that?