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 IHi PaulOlding
Thanks for the solution. However, this is again giving me previous values for other dates & not for the last date of the previous month when I choose the first date of selected month. for example, if i select July 1, it should give me previous values for June 30th. However since I need a slicer for month, I am getting a blank for this criteria.
Solution SageDataAnalyst_99 Which columns are you using for the Year and Month slicers?
- DataAnalyst_99
I 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.