DATEADD with 2 sets of intervals?

[abloor]

Hi,

I'm wondering if it's possble to do a DATEADD function for -1 month AND -1 day in the same measure.

eg I only get data on weekdays.  I have data for 26th Aug, but not 1 month prior, which is 26th Jul, so I'd want it to give me the data for 27th Jul which is the next available weekday (i.e. same period last month minus 1 day).

I have this measure which give me the date 1 month ago, but I was wanting to add the -1 day part into it as well.  Is this possible?

LOOKUPVALUE ('Table'[SnapDate], 'Table'[SnapDate], DATEADD('Table'[SnapDate], -1, MONTH) )

Thank you

[Greg_Deckler]

@abloor - Did one of these answer your question. If so, please accept as answer or @ me. Thanks!

[abloor]

Thank you very much @Greg_Deckler  @amitchandak  @v-zhenbw-msft for the time you have taken to reply to my post.

I realised that when I spoke of '-1' day, it's actually +1 day that I need, so I amended your DAX @Greg_Deckler  to be +1 at the end.

I had some mixed results with the solutions:

@amitchandak  I tried to implement your solution but didn't quite understand how  and where to use the DAX.  I tried it within a measure and a column in both my data and date tables but wasn't able to make it work unfortunately.

@Greg_Deckler This solution provided me with the correct data in some instances.  E.g. if a snap date was available 1 month prior to that selected in my snap date slicer, then the solution provided me with the correct data for that date +1 day as expected.  However if no data file was available for snap date 1 month prior (eg if the day 1 month prior was a weekend), then it was unable to provide me with the +1 day data needed.

@v-zhenbw-msft  This solution provided me with the correct data one month + 1 day prior to TODAY'S date.  When I tried to use it with a Snap Date slicer on the page, the data was blank.  Would it be easy to tweak the DAX to get it to work with a slicer?

Thanks again to you all!

[v-zhenbw-msft]

Hi @abloor ,

Please allow me to confirm whether my understanding is correct.

Do you want to get the same date last month plus one day, then find the value of the corresponding date?

If yes, we can create a measure to meet your requirement.

Measure = 
var _last_month = DATE(YEAR(MAX('Table'[SnapDate])),MONTH(MAX('Table'[SnapDate]))-1,DAY(MAX('Table'[SnapDate]))+1)
return
CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'),'Table'[SnapDate] = _last_month))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@abloor , Try like

Date as date add

date(year('Table'[SnapDate]),month('Table'[SnapDate]) -1 ,day('Table'[SnapDate])-1)

https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power...

[Greg_Deckler]

@abloor - Sure, you should be able to do this:

LOOKUPVALUE ('Table'[SnapDate], 'Table'[SnapDate], DATEADD('Table'[SnapDate], -1, MONTH)-1 )