Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Issues with DATESBETWEEN

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=hg6nihdnvvzy51sqorikmj965&st=bwdtxa53&dl=0


 
Need Help
  • HotChilli's avatar
    HotChilli
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    Hi Anonymous 

     

    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.

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous 

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you for your assistance.