Forum Discussion
Line Chart with Two Lines and One only displaying data for Months selected
I'm trying to create a line chart that compares actuals to budget by month.
I want the actual months to only show the line for the months from 1 to the month selected whereas the budget data would show all months for the year.
There are other charts on the report that will only be used to display a specific period's data and there are slicers for year and period.
If the user selects February, the line chart would show 12 months of budget data for the selected year and two months of actual data.
Is there a way to do that even if the user selects a specific month from the slicer?
16 Replies
Here you go. This is sample data I created.
Scenario Date Amount Budget 1/1/2025 1000 Budget 2/1/2025 1200 Budget 3/1/2025 1300 Budget 4/1/2025 1400 Budget 5/1/2025 1500 Budget 6/1/2025 1640 Budget 7/1/2025 1760 Budget 8/1/2025 1880 Budget 9/1/2025 2000 Budget ######## 2120 Budget ######## 2240 Budget ######## 2360 Actual 1/1/2025 900 Actual 2/1/2025 1250 There is also a date table. They use a standard calendar year. The date table has a year colum and a month column, in addition to the date column.
Okay. I goofed up on the original question. Let me restate the issue.
The data being displayed is a YTD value per month of budget and actual amounts.
What we want is, if the user selects 2025 as the year and February as the month, the line chart will display 12 months of target data and two months of actual data. Again, the amounts are YTD. I understand why it's displaying 12 months of actual data by default. But, if there's a way to cut off the actual line at the month selected by the slicer so it only shows a line from period 1 until the selected month, that's what is desired.
In the chart below, the bottom line is the YTD actual amount. It is showing 12 months of data but we want it to stop at the selected month. The top line, the YTD budget amount, should display 12 months of data.
In this case, all months were selected because I can't find a way to display 12 months of budget data and X months of actual data.
Hi callenbkd
Would the following measures help? (Note that I added a date table.)
Actual = CALCULATE( SUM ( 'Table'[Amount] ), 'Table'[Scenario] = "Actual" ) Budget = CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Scenario] = "Budget" ) Actual YTD = CALCULATE( [Actual], DATESYTD( 'Date'[Date] ), 'Date'[MonthNo] <= MAX( 'Month'[Month] ) ) Budget YTD = CALCULATE( [Budget], DATESYTD( 'Date'[Date] ) )Let me know if you have any questions.
Sample Show Actual And Budget - 2.pbix
Thank you for your help. I didn't see the response until today. The misunderstanding here is it appears the amounts are based on the period selected. However, what the amounts should be are the YTD amounts. When changing the amounts to YTD amounts, the actual line displays for all periods. I want it to stop at the selected month.
If you read all the replies I already gave the solution for it.
