Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Visualize costs dynamically

Hi,   I have two columns which are costs and period. The period ranges from 2018 Jan to 2020 Jan. Each month I add new column to the excel source with new date and cost. I want to visualize the las...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

Pragati11's avatar
Pragati11
Super User
6 years ago

Hi Anonymous ,

 

 Can you please provide some screenshots of sample data along with the visualisations that you want to achieve?

 

Thanks,

Pragati

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Pragati11,

 

I want to visualize something like this. I have done this by filtering but I want the graph get updated as I add the next month in my source file (excel). So, if I add February 2020 below the table, then the first picture shoul automatically show the period March 2019 - February 2020, and the second graph should show March 2018 - February 2019. The tableVisualizations

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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?