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]),
ALLSELECTED(data[Created Date]),
KEEPFILTERS(data[Created Date] < SELECTEDVALUE(data[Created Date]))
)
var _Index = CALCULATE( [Current IndexValue],
FILTER(ALL(data[Created Date]),
data[Created Date] = _PrevDate)
)
return _Index

the issue I am facing is that I have 2 filters for year and month, which I need. and a date slicer that chooses the date for the selected month. However, when I choose the first date of the month, it should show me values for the previous month's last date, ignoring the year/month filters, but it doesnt do so and instead returns a blank column. else the measure is working well for any date in the month. also, the dates arent continuous (weekends + holiday dates arent there) ..

is there a way to solve this?

  • 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.

9 Replies

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

    Hi 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.

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

        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