Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper V
2 years ago

Values Contingency from the last non blank position

Hi All,

I have a table, where produtcs codes are given besides relevant YTD Costs.

 

COGS per Unit = AVERAGE(COGS[COGS])

 

The values are beeing calculated with a very simple measure, given above. COGS tables is related to main Date Table and Producs Table.

I would like to have kind of "Data Contingency", where all the blank spaces in the Matrix given above will be filled out by the "non blank" values from the previous year. Like given below in some exempary layouts:

 

Thanks in advance,

Please feel free to ask quetions regarding  possible clarifications from my side.

1 Reply

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

    Hi,

    I am not sure how your semantic model looks like, but please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to step further to get a solution for your semantic model.

     

     

     

     

    Expected result measure: =
VAR _t =
    FILTER (
        ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE ( COGS, 'Product'[Product], 'Calendar'[Year] ),
                'Calendar'[Year] <= MAX ( 'Calendar'[Year] )
            ),
            "avg", [COGS avg:]
        ),
        [avg] <> BLANK ()
    )
RETURN
    CALCULATE ( [COGS avg:], INDEX ( 1, _t, ORDERBY ( 'Calendar'[Year], DESC ) ) )