Forum Discussion

callenbkd's avatar
callenbkd
Icon for Helper II rankHelper II
1 year ago
Solved

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 wou...
Need Help
  • parry2k's avatar
    parry2k
    1 year ago

    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] ) ) 
         )
    )
callenbkd's avatar
callenbkd
Icon for Helper II rankHelper II
1 year ago

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's avatar
    gmsamborn
    Icon for Super User rankSuper User
    1 year ago

    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