Forum Discussion

DocDri's avatar
DocDri
Icon for Helper I rankHelper I
1 year ago

Use last available data in line chart

Hello,

maybe you can help me with another problem:

 

I have a table with project data (values) for different reporting periods like this

 

project | period | valueX | value Y

A | 2025.01 | 15 | 27
A | 2025.02 | 14 | 27
A | 2025.03 | 15 | 20
B | 2025.01 | 40 | 10
B | 2025.02 | 50 | 10
B | 2025.03 | 20 | 10
C | 2025.01 | 85 | 12
D | 2025.02 | 99 | 11

 

I need a line chart where the period is on the x-axis and shows valueX over all projects, which is nothing special and easy to implement. The thing is, if there is no data in a certain period (e.g. project C has only data until 2025.01) the data of the latest available period should be used for that project (e.g. 85 for project C in period 2025.02, 2025.03, and so on).

 

Thanks for any help!

 

Need Help

3 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi DocDri ,

     

    To achieve this in Power BI, you'll need a DAX measure that ensures the last available value is carried forward for each project when data is missing for a given period. First, make sure you have a Calendar Table that includes all periods, even those without data. Then, create a measure that searches for the latest available ValueX for each project and uses that value in periods where no data exists.

    ValueX_Filled =
VAR CurrentPeriod = SELECTEDVALUE('Calendar'[Period])
VAR CurrentProject = SELECTEDVALUE('Projects'[Project])

VAR LastAvailableValue =
    CALCULATE(
        MAX('Projects'[ValueX]),
        'Projects'[Period] <= CurrentPeriod,
        'Projects'[Project] = CurrentProject,
        ALL('Projects')
    )

RETURN
    LastAvailableValue

    This measure finds the most recent ValueX for each project by looking at all rows where the period is less than or equal to the selected period. The ALL('Projects') function ensures that the measure scans all previous data points without being restricted by filters. Once applied to a line chart, where Period from the Calendar Table is on the x-axis, the missing values will be replaced with the latest available data for each project, ensuring a smooth trend over time.

     

    Best regards,

    • DocDri's avatar
      DocDri
      Icon for Helper I rankHelper I
      1 year ago

      Hi! Thank you very much for your hint. One question: Does this measure really returns the last available value? For me it seems that it returns the biggest value (  MAX('Projects'[ValueX]) ) where 'Projects'[Period] <= CurrentPeriod. That must not be the latest, or am I wrong?