DATESBETWEEN how to ignore filters

[MrMarshall]

I have the following for my 12Month trailing measure.

12Mo Trailing Sales = 
CALCULATE (
    [1Sales],
    DATESBETWEEN (
        'Value Entry'[Posting Date].[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ) ),
        LASTDATE ( 'Value Entry'[Posting Date].[Date] )
))

Works great if I want to look on all the years I have data on, exept for the first year...

When adding a filter in the report, for example only look on year 2018, the measure ignores 2017 and the trailing measure is for 2018 is incorrect, since it isn't using the data for 2017. 

ALso tried to ignore filters for the data that DATESBETWEEN uses:

12Mo Trailing Sales =  
CALCULATE (
    [1Sales],
    DATESBETWEEN (
        ALL('Value Entry', 'Value Entry'[Posting Date].[Date]),
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ) ),
        LASTDATE ( 'Value Entry'[Posting Date].[Date] )
))

But it gives me error:

DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument.

Any ideas?

[v-yulgu-msft · Accepted solution]

Hi @MrMarshall,

Maybe you can try to create a calendar table with continual dates, create a relationship between calendar table and data table ('Value Entry'). Refer to date column in calendar table rather than 'Value Entry' in your measure. Also, add dates from calendar table into Matrix and slicer.

Regards,

Yuliana Gu

[vonne_L]

Hi Yuliana

@v-yulgu-msft , this worked. 

Can you eleborate why it works when we refer to the date in the calendar table ( in the measure, slicer & matrix)?

thanks.

[MrMarshall]

Worked!

12Mo Trailing Sales =  
CALCULATE (
    [1Sales],
    DATESBETWEEN (
        'Value Entry'[Posting Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ) ),
        LASTDATE ( 'Value Entry'[Posting Date].[Date] )
))

A calendar table with the DAX above. 
Thank you! 

[rajulshah]

Hello @MrMarshall,

Try using only column,i..e. 'Value Entry'[Posting Date].

Hope this helps.

[MrMarshall]

Hi! I am afraid that didn't help. 

[rajulshah]

Try using the following formula:

12Mo Trailing Sales =  
CALCULATE (
    [1Sales],
    DATESBETWEEN (
        'Value Entry'[Posting Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Value Entry'[Posting Date].[Date] ) ) ),
        LASTDATE ( 'Value Entry'[Posting Date].[Date] )
))

[MrMarshall]

Hi!
Thanks for the reply.

Using the formula above gets me the same result for the trailing value and the "normal" sales value.
So unfortunately not a trailing value.