Please help!

[DataAnalyst_99]

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?

[DataAnalyst_99]

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.

[PaulOlding]

@DataAnalyst_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 · Accepted solution]

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

[ValtteriN]

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]

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]

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