Forum Discussion
Helper IIDate limit columns differently in one data visual
I have a visual that is showing the status of 4 columns. It is easy to date limit the whole table by dropping the data into the filter for the visual or the page, but I need to date filter all the columns with a different start date 'Column A' differently than I filter 'Column B' Guessing I need to do this in the Calculated column but am struggling with how to do it most efficiently.
Value = CALCULATE(SUM('ColumnValue'/COUNT('Columns), Column A starts 6/1/2022, Column B starts 7/1/2022, Column C starts 8/1/2022, Column D Starts 9/1/2022))
Any help is appreciated...
Hi ghutchins,
You need to create a new measure
Value =
Var selectedColumn = SELECTEDVALUE(Table[Column])Var startDate = SWITCH(selectedColumn
, "Column A", DATE(2022, 6, 1)
, "Column B", DATE(2022, 7, 1)
, "Column C", DATE(2022, 8, 1)
, "Column D", DATE(2022, 9, 1)
)
Var endDate = MAX(Table[DateColumn])
RETURNCALCULATE(
SUM('ColumnValue') / COUNT('Columns)
, ALL(Table[DateColumn])
, Table[DateColumn] >= startDate
. Table[DateColumn] <= endDate
)
2 Replies
- ReneMoawad
Resolver III
Hi ghutchins,
You need to create a new measure
Value =
Var selectedColumn = SELECTEDVALUE(Table[Column])Var startDate = SWITCH(selectedColumn
, "Column A", DATE(2022, 6, 1)
, "Column B", DATE(2022, 7, 1)
, "Column C", DATE(2022, 8, 1)
, "Column D", DATE(2022, 9, 1)
)
Var endDate = MAX(Table[DateColumn])
RETURNCALCULATE(
SUM('ColumnValue') / COUNT('Columns)
, ALL(Table[DateColumn])
, Table[DateColumn] >= startDate
. Table[DateColumn] <= endDate
)
ghutchinsThanks... that was the answer i was looking for!