Issues with DATESBETWEEN

[ThangNT]

I am trying to calculate a moving average from 12/31/2014 to the present using CALCULATE, AVERAGEX, and DATESBETWEEN functions as following:

MA Accounts Receivable from Customers =

CALCULATE(

    Averagex(

        BalanceSheet,

        [Accounts Receivable from Customers]

    ),

    DATESBETWEEN(

        'Date'[Date Key],

        Date(2014,12,31),

        Max('Date'[Date Key])

    )

)

However, the results still include values for 3/31/2014, 6/30/2014, and 9/30/2014, which I did not intend. Could you please help me understand why this is happening?  

Thank you in advance for your assistance!
https://www.dropbox.com/scl/fi/tdfsaezoauqma9fdddmb9/Company-Analysis.pbix?rlkey=hg6nihdnvvzy51sqori...

 

[HotChilli · Accepted solution]

I haven't checked the numbers but the reason these dates are included is because the model is using Auto date/time option, i.e. date tables are created in the background and these are included in your hierarchy.

So you can switch off Auto date/time and create your own date hierarchy.

--

I also have an opinion on the date table used in the model - it's not a date table in the true sense i.e. contains all dates over a period.  DAX date functions are really best used with  proper date tables.  The model and measures may work for you (which is great) but please be aware of the possible problems.

[Anonymous · Accepted solution]

Hi @ThangNT 

I did some change on your measure, please try this:

MA Accounts Receivable from Customers =
IF (
    MAX ( 'Date'[Date Key] ) < DATE ( 2014, 12, 31 ),
    BLANK (),
    CALCULATE (
        AVERAGEX ( BalanceSheet, [Accounts Receivable from Customers] ),
        DATESBETWEEN (
            'Date'[Date Key],
            DATE ( 2014, 12, 31 ),
            MAX ( 'Date'[Date Key] )
        )
    )
)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ThangNT]

Thank you for your assistance.

[ThangNT]

Thank you for your assistance. I have successfully created my own date hierarchy, which is functioning as expected. The structure of my date table is tailored to financial statements of listed companies, so it includes only the dates 31/3, 30/6, 30/9, and 31/12 for each year.

[HotChilli · Accepted solution]

I haven't checked the numbers but the reason these dates are included is because the model is using Auto date/time option, i.e. date tables are created in the background and these are included in your hierarchy.

So you can switch off Auto date/time and create your own date hierarchy.

--

I also have an opinion on the date table used in the model - it's not a date table in the true sense i.e. contains all dates over a period.  DAX date functions are really best used with  proper date tables.  The model and measures may work for you (which is great) but please be aware of the possible problems.