Forum Discussion

rammishra's avatar
rammishra
Icon for Helper II rankHelper II
6 years ago

Measure to use most recent values available if data are missing

Dear Community Members

 

Please help me in solving a problem that looks simple, but I am stuck with it. I am calculating a measure (a score) using the values of indicators for my states. My report has a slicer where users will choose a year to get the score for that particular year. In my data, year is entered as text in the form of: 2011-12, 2015-16, 2016-17, etc.

 

The measure works well as long as we have data for the indicator in the selected year. If the indicator values are missing for the selected year, my measure is missing for that year too. What I want is that my measure should include the most recent value of the indicator in case the indicator is missing for the selected year. For instance, if user selects 2016-17 the indicator has values only till 2015-16, then the measure should consider the value for 2015-16 to calculate the score.  Following is the DAX code for my measure (sample data are attached).

 

Test measure =
VAR x =
CALCULATE (
MAX ( Data[Value] ),
FILTER (
ALLEXCEPT ( Data, Data[State], Data[Year] ),
Data[Indicator No.] = "4.5.2"
)
)
VAR minvalue =
CALCULATE (
MIN ( Data[Value] ),
FILTER ( ALLEXCEPT ( Data, Data[Year] ), Data[Indicator No.] = "4.5.2" )
)
VAR num = ( x - minvalue )
VAR denom = ( 1 - minvalue )
RETURN
DIVIDE ( num, denom )
 
 
 
 
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    rammishra , Try like

    if value is measure

    lastnonblankvalue(Table[Year],[Value])

    or
    lastnonblankvalue(Table[Year],sum([Value]))

    or

    calculate(lastnonblankvalue(Table[Year],[Value]), allexcept(Table, Table[state], Table[Indicator No]))

    • rammishra's avatar
      rammishra
      Icon for Helper II rankHelper II
      6 years ago

      Thanks for your prompt response. 

       

      I am not able to write the DAX code to calculate two variables that are needed to calculate the scores (with the logical DAX statement that you suggested).

       

      The first variable calculates the value of the indicator for states in the selected year.

       

      The second variable calculates the minimum value of the indicator (across all states) in the selected year.

       

      It will be very helpful if you can kindly help me to write the DAX codes to calculate these two variables with the logic that if values are missing for the selected year, the measure should consider the latest values for that indicator.

       

      Thanks and regards

      Ram

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    hI rammishra ,

    According to my understand, you want to fill the place where the value is blank: the value of the most recent year that is not blank(),right?

    You could use the following formula:

    yearColumn =
LEFT ( 'DataTable'[Year], 4 )
    Column =
VAR _latestyear =
    CALCULATE (
        MAX ( 'DataTable'[yearColumn] ),
        FILTER (
            ALLEXCEPT ( 'DataTable', 'DataTable'[Indicator No,], 'DataTable'[State] ),
            'DataTable'[yearColumn] < EARLIER ( [yearColumn] )
                && 'DataTable'[Value] <> BLANK ()
        )
    )
RETURN
    IF (
        'DataTable'[Value] = BLANK (),
        CALCULATE (
            SUM ( 'DataTable'[Value] ),
            FILTER (
                ALLEXCEPT ( 'DataTable', 'DataTable'[Indicator No,], 'DataTable'[State] ),
                'DataTable'[yearColumn] = _latestyear
            )
        ),
        'DataTable'[Value]
)

    My visualization looks like this:

    Did I answer your question? Please mark my reply as solution ,thank you.

    If you have any questions, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin