Forum Discussion

DataAnalyst_99's avatar
DataAnalyst_99
Icon for Helper I rankHelper I
4 years ago
Solved

Please help!

I have to calculate the previous day's values from my table, for which I have created a measure and it is working fine  previousdates = var _PrevDate = calculate( MAX(data[Created Date]), ALL...
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    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's avatar
DataAnalyst_99
Icon for Helper I rankHelper I
4 years ago

Hi ValtteriN,

Thank you for your solution. However it didnt work for me..

 

 

As you can see, for the first date of the month, I am getting no values for the previous date..
if I change the dates, I do get values..


 

PaulOlding's avatar
PaulOlding
Icon for Solution Sage rankSolution Sage
4 years ago

Hi DataAnalyst_99 

The behaviour suggests the year and month is in the filter context when _PrevDate is being calculated.  If you select the 1st of the month there is no previous date in the same month.

Perhaps a revised _PrevDate will work

var _PrevDate =
calculate(
    MAX(data[Created Date]),
    data[Created Date] < SELECTEDVALUE(data[Created Date])
)