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:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,896
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,561
Messages
5,832,512
Members
430,138
Latest member
silver_john

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