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.

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

Hi,

Try something like this:

data:


Dax:

Previousdate value =
var _pdate = CALCULATE(MAX(Cumulativetotal[Date]),ALL(Cumulativetotal[Date]),Cumulativetotal[Date]=MAX('Calendar'[Date])-1)
return

CALCULATE(SUM(Cumulativetotal[Value]),all(Cumulativetotal[Date]),Cumulativetotal[Date]=_pdate)

end result:

Note that the value returned is 30.6.2022 from test data -> the function works

In general I would avoid using multiple date filters since they will often conduse end-users.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • 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])
)