Forum Discussion

anshenterprice's avatar
anshenterprice
Icon for Helper I rankHelper I
2 years ago

Fill in missing values in a line graph with max date value

Hi Team,

CategoryDateNAVEquity
ABC9/10/2024250 
ABC9/9/2024201 
ABC9/6/2024152 
ABC9/5/2024103 
ABC9/4/202454 
ABC9/3/202420 
ABC9/2/202440 
ABC5/15/2024280300
ABC4/25/2024208201
ABC1/20/2024201207
XYZ9/10/2024230250
XYZ9/9/2024201 
XYZ9/6/2024152 
XYZ9/5/2024103 
XYZ9/4/2024154 
XYZ9/3/2024120 
XYZ9/2/2024140 
XYZ5/10/2024290300
XYZ4/10/2024208201
XYZ1/20/2024240207

 


there are two slicer
Slicer1-Category Slicer
Slicer2- another is name column that i created as a table with that unique fields to use in the slicer like Equity,Nav ..Etc...


Grapgh- line chart

x-axis- Date
Y-axis- Line graph Selected Measure Name
legend- Name

Measure-
Line graph Selected Measure Name=
VAR MySelection =
SELECTEDVALUE ( NameTable(Name))
RETURN
SWITCH (
TRUE (),
MySelection = "NAV", SUM(sourceTable[NAV]])/100,
MySelection = "Equity", SUM(sourceTable[Equity])/100,

SUM(sourceTable[NAV] )



but the problem is we have some missing data in some of the fields
like we have all the data in the NAV column with all dates
but in the other fields like Equity column we have limited data

Our objective is to make a consistant line, it means that whatever the max date of equity and the value that should display with rest of other dates values till our NAV max date so lets consider 5/15/2024 is the max date and value is 300 once we select ABC from the slicer then our line graph it should show 300 value from 5/15/2024 to 9/10/2024 date

Thanks



 

General Comment
Need Help
Show and Tell
Tips and Tricks

5 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello anshenterprice,

     

    Can you please try this approach:

     

    1. Create a Measure for Max Value of the Last Date

    LastAvailableValue = 
VAR MaxDate = 
    CALCULATE(
        MAX(SourceData[Date]),
        ALLEXCEPT(SourceData, SourceData[Category], SourceData[Name])
    )
RETURN
CALCULATE(
    MAX(SourceData[Value]),
    SourceData[Date] = MaxDate
)

    2. Create a Measure for the Line Graph

    LineGraphValue = 
VAR SelectedName = SELECTEDVALUE(NameTable[Name])
VAR MaxNavDate = CALCULATE(MAX(SourceData[Date]), SourceData[Name] = "NAV")
VAR CurrentValue = SUM(SourceData[Value])
VAR LastValue = [LastAvailableValue]
RETURN
IF (
    ISBLANK(CurrentValue) && SELECTEDVALUE(SourceData[Date]) > MAX(SourceData[Date]), 
    LastValue, 
    CurrentValue
)
  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    That wouldn't really agree with the actual data?

    You can do a Line Style "Step-After"  to emulate that behavior.

     

     

     

  • anshenterprice's avatar
    anshenterprice
    Icon for Helper I rankHelper I
    2 years ago

    Sahir_Maharaj lbendlin Sorry, About the wrong sourcedataTable

    here is the actual table

    CategoryDateNAVEquity
    ABC9/10/2024250 
    ABC9/9/2024201 
    ABC9/6/2024152 
    ABC9/5/2024103 
    ABC9/4/202454 
    ABC9/3/202420 
    ABC9/2/202440 
    ABC5/15/2024280300
    ABC4/25/2024208201
    ABC1/20/2024201207
    XYZ9/10/2024230250
    XYZ9/9/2024201 
    XYZ9/6/2024152 
    XYZ9/5/2024103 
    XYZ9/4/2024154 
    XYZ9/3/2024120 
    XYZ9/2/2024140 
    XYZ5/10/2024290300
    XYZ4/10/2024208201
    XYZ1/20/2024240207
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks for the reply from lbendlin ,  Sahir_Maharaj, please allow me to provide another insight:
      Hi anshenterprice ,

      Based on the data you provided, I ran tests.

      When I check ABC and Equity, the line in the line chart stops at 5/15/2024 because the Equity values after that date are all null.

       

      Therefore, would you like to draw a line for these dates that have no Equity value as well?
      If so, changes can be made to your Line graph Selected Measure Name measure.
      If Equity is null, then this Equity is 0.

      Line graph Selected Measure Name = 
VAR MySelection =
SELECTEDVALUE (NameTable[Name])
RETURN
SWITCH(
TRUE(),
MySelection = "NAV", SUM(sourceTable[NAV])/100,
MySelection = "Equity", 
IF(ISBLANK(SUM(sourceTable[Equity])),0,SUM(sourceTable[Equity])/100),
SUM(sourceTable[NAV]))


      In this way, a continuous line can be plotted for Equity up to the max date in the line chart.


      Please see the attached pbix for reference.

      Best Regards,
      Dengliang Li

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

        Thank you Anonymous Actually it should display 3 instead of 0 incase if its blank on equity data after max date values.. so whatever max date values that should go at the end (max date of NAV)
        because in the above dax whenever its blank then it should display 0 but we dont want that ..we are only focusing on the max date value of equity