Forum Discussion
Helper IPlease help!
Try using REMOVEFILTERS to explicitly remove the filters on Year and Month
var _PrevDate = calculate( MAX(data[Created Date]), data[Created Date] < SELECTEDVALUE(data[Created Date]), REMOVEFILTERS(<Year Column>), REMOVEFILTERS(<Month Column>) )You'll probably need the REMOVEFILTERS on the _Index calculation too.
Best practice here would still be to use a date table. You could use LASTNONBLANKVALUE to go back to the previous date that exists in the data.
Helper II did try ValtteriN solution which has a calendar date part of the dax formula and so created a calendar table, but since there are a lot of missing date values, it essentially failed. Also, it didnt solve my query for the previous date value for the first date of selected month
Solution SageTry using REMOVEFILTERS to explicitly remove the filters on Year and Month
var _PrevDate =
calculate(
MAX(data[Created Date]),
data[Created Date] < SELECTEDVALUE(data[Created Date]),
REMOVEFILTERS(<Year Column>),
REMOVEFILTERS(<Month Column>)
)You'll probably need the REMOVEFILTERS on the _Index calculation too.
Best practice here would still be to use a date table. You could use LASTNONBLANKVALUE to go back to the previous date that exists in the data.
- DataAnalyst_99
PaulOlding - That worked! Thank you!
Final Dax:
previousdates =
var _PrevDate =
calculate(
MAX(data[Created Date]),
data[Created Date] < SELECTEDVALUE(data[Created Date]),
REMOVEFILTERS(data[Created Date].[Year]),
REMOVEFILTERS(data[Created Date].[Month])
)
var _Index = CALCULATE( [Current IndexValue],
FILTER(ALL(data[Created Date]),
data[Created Date] = _PrevDate) ]),
REMOVEFILTERS(data[Created Date].[Year]),
REMOVEFILTERS(data[Created Date].[Month])
)
return _Index