Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Offset function combined with field parameter

Hello,

 

I would like to create a table that will show a change between 2 dates but requirement from the user is to be able to dynamically change time perspective view - so they would like to compare year to year / month to month / week to week / quarter to quarter / day over day. Since there is also week over week needed I cannot use built in time intelligence function. Till now I used additional table with dynamic date selection, however I thought that maybe it will be possible to write a metric with offset function and in the table itselt I could use field parameter. I created it but received info: Column [] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.
My metric is written as follows:

 

Total Change = 
VAR Offset = 
CALCULATE(
    [Total],
    OFFSET(
        -1,
        ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]),
        ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC)
        )
    )
VAR PoPChange = 
([Total] - 
CALCULATE(
    [Total],
    OFFSET(
        -1,
        ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]),
        ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC)
        )
    )) / CALCULATE(
    [Total],
    OFFSET(
        -1,
        ALLSELECTED('Dynamic Date Selection'[Dynamic Date Selection]),
        ORDERBY('Dynamic Date Selection'[Dynamic Date Selection], ASC)
        )
    )
    VAR Result = IF(NOT ISBLANK(Offset), PoPChange)
    Return
    Result

 

 

Dynamic date selection is a table created by using field parameter function:
|

 

Dynamic Date Selection = {
    ("calendar_date", NAMEOF('calendar'[calendar_date]), 0),
    ("week_end_date", NAMEOF('calendar'[week_end_date]), 1),
    ("month_end_date", NAMEOF('calendar'[month_end_date]), 2),
    ("year_no", NAMEOF('calendar'[year_no]), 3)
}

 

 

Is it even possible to make changes in Change metric to be able to use it in table with field parameter in rows?

3 Replies