Forum Discussion

anshenterprice's avatar
anshenterprice
Helper I
1 year ago

Dynamically show missing values in a line graph with max date value of particular fields

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi anshenterprice ,

     

    Try to modify your formula and create slicer table with not relationship like below:

     

    Result = 
VAR MySelection =
    SELECTEDVALUE ( NameTable[Name] )
VAR MaxEquityDate =
    CALCULATE ( MAX ( sourceTable[Date] ), NOT ( ISBLANK ( sourceTable[Equity] ) ) )
VAR MaxEquityValue =
    CALCULATE ( SUM ( sourceTable[Equity] ), sourceTable[Date] = MaxEquityDate )
RETURN
    SWITCH (
        TRUE (),
        MySelection = "NAV", SUM ( sourceTable[NAV] ) / 100,
        MySelection = "Equity",
            IF (
                ISBLANK ( SUM ( sourceTable[Equity] ) ),
                MaxEquityValue / 100,
                SUM ( sourceTable[Equity] ) / 100
            ),
        SUM ( sourceTable[NAV] ) / 100
    )

     

     

     

    Best Regards,
    Adamk Kong

     

    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
    Helper I
    1 year ago

    Anonymous 
    Still its not working, Can you please select two equity and nav also from the slicer