Line Chart with Two Lines and One only displaying data for Months selected

[callenbkd]

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? 

[v-achippa]

Hi @callenbkd,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @parry2k and @gmsamborn for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @callenbkd,

We wanted to kindly follow up to check if the solution provided by the super user resolved your issue.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @callenbkd,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[callenbkd]

Somehow, I did not see the additional responses. I don't know how I missed them. I'll give them a shot. 

[parry2k]

If you read all the replies I already gave the solution for it. 

[callenbkd]

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. 

[parry2k · Accepted solution]

@callenbkd good solution from @gmsamborn but to avoid flat line for actual, use following measure:

Actual YTD = 
    CALCULATE(
        [Actual],
        DATESYTD ( 
            CALCULATETABLE ( VALUES ( 'Date'[Date] ),  'Date'[MonthNo] <= MAX( 'Month'[Month] ) ) 
         )
    )

[gmsamborn]

@parry2k 

Thanks for that.  After-re-reading, I realize that I had the requirement wrong thinking they wanted to see the flat line.

[callenbkd]

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. 

[gmsamborn]

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

[callenbkd]

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. 

[parry2k]

@callenbkd I have already provided the solution, not sure what this sample data is?

[callenbkd]

Sample Data 

[parry2k]

@callenbkd See attached (page2), demo how you can achieve this, tweak the solution as you see fit.

[callenbkd]

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

[parry2k]

@callenbkd could you share sample pbix file, share using one drive/google drive. make sure to remove any sensitive information before sharing, maybe create some dummy data.