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.
Solution SageDataAnalyst_99 Which columns are you using for the Year and Month slicers?
Helper II havent created a date table if that is what you are asking since a lot of dates are missing in my data..so the year & month values are coming from my data itself
- DataAnalyst_99
I 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
- PaulOlding
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.
- 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