Forum Discussion
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 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!
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.
6 Replies
Hi Anonymous ,
Can you please provide some screenshots of sample data along with the visualisations that you want to achieve?
Thanks,
Pragati
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
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.
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/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!
