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?
