# Making Dateadd return EXACTLY one month before (calculation @ 29/3 30/3 31/3 returns value at 28/2)

#### LRMMF

##### New Member
Hi,

i've developed this calculation to use in a model where 'dim posting date' as an hierarchy year|month|date. It allows me to:

1) if a month is selected and no date is selected, it does the calculation for Month

2) if a month is selected AND dates are selected, it does the calculation for the day. (already changed to allow non contiguous dates)

3) Finally, other situation is only the year selected, and in this case i want to appear BLANK.

But now i have a problem, in 2), at 29, 30 and 31 of March i get the (last) value of 28 February. I don't want that, How can i change the calculation to make it exactly return the value for one month before. If the day doesn't exist, i want blank to appear.

(the "opposite" happens without problems: at 28 February i get the value of 28 January)

Sales Amount M-1 :=
IF (
HASONEVALUE ( 'Dim Posting Date'[Month] )
&& NOT ISFILTERED ( 'Dim Posting Date'[Date] );
CALCULATE (
[Sales Amount];
DATESMTD ( DATEADD ( 'Dim Posting Date'[Date]; -1; MONTH ) )
);
IF (
HASONEVALUE ( 'Dim Posting Date'[Month] )
&& ISFILTERED ( 'Dim Posting Date'[Date] );
SUMX (
VALUES ( 'Dim Posting Date'[Date] );
CALCULATE (
[Sales Amount];
DATEADD ( 'Dim Posting Date'[Date]; -1; MONTH )
)
);
BLANK ()
)
)

Last edited:

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Code:
``````Option Explicit

Function ReturnPreviousMonthDate(dteInput) As Variant

Dim dtePrevious As Date
dtePrevious = DateSerial(Year(dteInput), Month(dteInput) - 1, Day(dteInput))
If Day(dteInput) = Day(dtePrevious) Then
ReturnPreviousMonthDate = dtePrevious
Else
ReturnPreviousMonthDate = vbNullString
End If

End Function``````

Thx for trying to help Phil -- you ended up in the Power BI forum where your answer probably won't work out.

Apologies LRMMF, been slammed lately, and haven't had time to help out here... and don't have time to properly look into your issue. I can give a few thoughts though, in hopes of a push in the right direction.

* I think you are fine here, but just to double check... when using time intelligence functions you must have a calendar table w/ a continuous date range -- gaps will be bad.
* Frequently, I ended up writing measures like... [Sales - YTD] := IF ([Sales], <ytd calc>) ... so "If I have sales this month, then show me the YTD, otherwise just show blank".
* When I need to give up on built in calendar functions... I typically end up creating helper columns in my calendar table, then writing measures to use those. So, maybe a "Day Id" and "Day Id For Last Month" ... and that latter column maps Mar 20 to Feb 20... but Mar 30 to BLANK().

Thx for trying to help Phil -- you ended up in the Power BI forum where your answer probably won't work out.

Apologies LRMMF, been slammed lately, and haven't had time to help out here... and don't have time to properly look into your issue. I can give a few thoughts though, in hopes of a push in the right direction.

* I think you are fine here, but just to double check... when using time intelligence functions you must have a calendar table w/ a continuous date range -- gaps will be bad.
* Frequently, I ended up writing measures like... [Sales - YTD] := IF ([Sales], <ytd calc>) ... so "If I have sales this month, then show me the YTD, otherwise just show blank".
* When I need to give up on built in calendar functions... I typically end up creating helper columns in my calendar table, then writing measures to use those. So, maybe a "Day Id" and "Day Id For Last Month" ... and that latter column maps Mar 20 to Feb 20... but Mar 30 to BLANK().

Hi, yes, thanks Phil but my doubt is in dax language.
I have a calendar table with continuous date range - check, i think that something in the code makes this condition fail.

Replies
0
Views
525
Replies
3
Views
595
Replies
1
Views
260
Replies
2
Views
581
Replies
1
Views
1K

1,211,748
Messages
6,103,686
Members
447,877
Latest member
OrangeJuice

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back