Forum Discussion
Chart YTD for multiple years from monthly data
I have a data set with monthly sales data that I'm trying to chart in a YTD view. If I have only one "scenario" a year I can use the year in the legend and it would work perfectly. However, I have multiple scenarios (Actuals, budget, forecast, etc) that can be in a given year. In the image below, the top left is MTD, the top right is YTD and you can see that the 2021 Actuals series no longer shows up on this chart (presumably because I'm using the TotalYTD function and 2022 is the latest year in the data). The formula works and calculates the right amounts based on the numerical table below the charts as long as it can pass the year, then the year scenario to the calculation. Any ideas on which measure would make all scenarios from each year show up in the YTD chart?
Sample data used for the charts/table:
Month Scenario Amount
| Saturday, January 1, 2022 | 2022 Budget | 100 |
| Tuesday, February 1, 2022 | 2022 Budget | 100 |
| Tuesday, March 1, 2022 | 2022 Budget | 99 |
| Friday, April 1, 2022 | 2022 Budget | 97 |
| Sunday, May 1, 2022 | 2022 Budget | 97 |
| Wednesday, June 1, 2022 | 2022 Budget | 94 |
| Friday, July 1, 2022 | 2022 Budget | 91 |
| Monday, August 1, 2022 | 2022 Budget | 89 |
| Thursday, September 1, 2022 | 2022 Budget | 89 |
| Saturday, October 1, 2022 | 2022 Budget | 89 |
| Tuesday, November 1, 2022 | 2022 Budget | 89 |
| Thursday, December 1, 2022 | 2022 Budget | 89 |
| Saturday, January 1, 2022 | 2022 Actuals | 100 |
| Tuesday, February 1, 2022 | 2022 Actuals | 103 |
| Tuesday, March 1, 2022 | 2022 Actuals | 105 |
| Friday, April 1, 2022 | 2022 Actuals | 103 |
| Sunday, May 1, 2022 | 2022 Actuals | 103 |
| Wednesday, June 1, 2022 | 2022 Actuals | 104 |
| Friday, July 1, 2022 | 2022 Actuals | 102 |
| Monday, August 1, 2022 | 2022 Actuals | 101 |
| Thursday, September 1, 2022 | 2022 Actuals | 100 |
| Saturday, October 1, 2022 | 2022 Actuals | 101 |
| Tuesday, November 1, 2022 | 2022 Actuals | 102 |
| Thursday, December 1, 2022 | 2022 Actuals | 103 |
| Friday, January 1, 2021 | 2021 Actuals | 100 |
| Monday, February 1, 2021 | 2021 Actuals | 100 |
| Monday, March 1, 2021 | 2021 Actuals | 98 |
| Thursday, April 1, 2021 | 2021 Actuals | 97 |
| Saturday, May 1, 2021 | 2021 Actuals | 94 |
| Tuesday, June 1, 2021 | 2021 Actuals | 91 |
| Thursday, July 1, 2021 | 2021 Actuals | 89 |
| Sunday, August 1, 2021 | 2021 Actuals | 91 |
| Wednesday, September 1, 2021 | 2021 Actuals | 88 |
| Friday, October 1, 2021 | 2021 Actuals | 89 |
| Monday, November 1, 2021 | 2021 Actuals | 87 |
| Wednesday, December 1, 2021 | 2021 Actuals | 89 |
Hi,
It looks like the limitation in a line chart is that one cannot drag more than 1 measure to Y-axis. Therefore, i have used the Line and Clustered column chart. Can you live this with?
You may download the PBI file from here.
Hope this helps.
6 Replies
- amitchandak
Super User
bjw210 , As you month is in date format, or create a date from month, Join it with date table and then use dateytd
example
YTD Sales = CALCULATE(SUM(Table[Amount]),DATESYTD('Date'[Date],"12/31"))
you can also add a filter
YTD Sales = CALCULATE(SUM(Table[Amount]),DATESYTD('Date'[Date],"12/31"), filter(Table, Table[Scenario] = "Budget") )
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uASorry, had projects come up and just coming back to this one. I ended up doing something similar to what you're talking about but running into a seperate problem.
Using the DatesYTD seems to only work for the most recent year if multiple years exist where i need to pass filters. When I use the filter funtion, I can pass the right year to the measure but it won't calculate for periods that don't have transactions.
In the below,- I can get DatesYTD to work for both actuals and budget for the most recent year in the table, but doesn't work for prior years (end table will have data back 5+ years so I wouldnt' be able to use SAMEPERIODLASTYEAR). DatesYTD seems to calculate across all months even when no transactions exist
- I can get Filters to work for all periods and scenarios however, it does not calculate for periods that did not have a transaction. This means a month with no transaction would show 0 for YTD which would be wrong. The Filter Column in the below should show amounts in each month regardless of having a transaction.
DatesYTD =CALCULATE(SUM(Sheet2[Amount]),DATESYTD(Dates[Date]))Filter =Var __Year = LEFT(SELECTEDVALUE(Sheet2[Scenario]),4)Var __Month = LOOKUPVALUE(Dates[Month Number],Dates[Month Name],SELECTEDVALUE(Dates[Month Name]))Var __Date = DATE(__Year,__Month,1)Var __Result =CALCULATE(SUM(Sheet2[Amount]),FILTER(ALL(Dates),Dates[Year]=__Year && Dates[Date]<=__Date))Return __Result
- Ashish_Mathur
Hi,
Based on that data that you have shared, please show the expected result in a Table format.
Below is the expected Chart which would have all 3 scenarios on it. 2022 Actuals, 2022 Budget, and 2021 Actuals. I can only seem to get one year of scenarios to show up on the chart at one time.
- Ashish_Mathur
Hi,
It looks like the limitation in a line chart is that one cannot drag more than 1 measure to Y-axis. Therefore, i have used the Line and Clustered column chart. Can you live this with?
You may download the PBI file from here.
Hope this helps.
