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

[LRMMF]

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

 

[pbornemeier]

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]

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]

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.