Visualize costs dynamically

[Anonymous]

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 last 12 month in a line graph which will be updated as I add new month every time. In another graph I want to visualize the period between the last 24 month from the last date and 12 months from the last date. So for now the first graph should show period 2019 Jan - 2020 Jan and the second graph should show 2018 Jan - 2019 Jan. When I add 2020 Feb to the model, they shoud automatically show  2019 Feb- 2020 Feb and 2018 Feb- 2019 Feb respectively. How can I do that?

Thanks in advance!

[amitchandak]

One way is that you use the visual level date filter and use the relative date filter to have last 12 months, last 24 month including today.

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,Month))  
Rolling 24 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-24,Month))  

 You can need to use date calendar for this

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Hi @amitchandak,

I do not know why but the first measure shows the whole period not the last 12 month, and it takes cumulative sum of the all periods. 

I want the second measure to show the period between 24 months before last date and 12 month before the last date. Not whole 24 month before the last date. 

Thanks!

[Pragati11]

Hi @Anonymous ,

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

Thanks,

Pragati

[Anonymous]

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 · Accepted solution]

@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]

@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?