cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

DATEADD with 2 sets of intervals?

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

5 REPLIES 5
Highlighted
Super User IV
Super User IV

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

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

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

 

Date1.jpg

 

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.

Highlighted
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors