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

LRMMF

New Member
Joined
Mar 5, 2015
Messages
16
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:

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,884
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
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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().
 

LRMMF

New Member
Joined
Mar 5, 2015
Messages
16
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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?

Disable AdBlock

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
Top