topic Problems with Calculate and DATESBETWEEN for Rolling calculation in DAX Commands and Tips

Problems with Calculate and DATESBETWEEN for Rolling calculation

Anonymous — Wed, 03 Nov 2021 17:57:34 GMT

Hi!

I am trying to calculate a 12 month cummulative sum of some values. I am using the formula:

=CALCULATE(

sum([WON]);

DATESBETWEEN(Table1[date];

dateadd(Table1[date];-1;YEAR);

Table1[date]))

Where fecha is a Date formated column in my dataset.

It's not working; datesbetween function doesn't seem to filter the dates and only retieves the same value all the time. Dateadd does work and refer to the Y-1 date.

I've tried this same formula in other dataset and it seems to do the trick. I've checked the date format in case it was a regional configuration issue and doesn't seem to be a problem. What can be wrong? I am getting the data via MySQL connector service but it shouldn't be a problem, should it?

I'll appreciate any help!

Javier

Re: Problems with Calculate and DATESBETWEEN for Rolling calculation

VahidDM — Sat, 06 Nov 2021 22:43:18 GMT

Hi Anonymous

Use DATESINPERIOD DAX function rather than DATESBETWEEN.

Try this:

=CALCULATE(

sum([WON]);

DATESINPERIOD(Table1[date];

MAX(Table1[date]);-1;YEAR)
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: Problems with Calculate and DATESBETWEEN for Rolling calculation

Anonymous — Mon, 08 Nov 2021 16:30:10 GMT

Thanks VahidDM !!

Now it seems to show a circular dependence error. How can this be?

Re: Problems with Calculate and DATESBETWEEN for Rolling calculation

Anonymous — Mon, 08 Nov 2021 17:04:56 GMT

It worked as a variable, thouhgh not in a row level. Will try some iterator functions!

Re: Problems with Calculate and DATESBETWEEN for Rolling calculation

VahidDM — Mon, 08 Nov 2021 23:15:26 GMT

Anonymous

Can you share a sample of your file [PBIX] after emoving sensetive data?

Appreciate your Kudos!!

Re: Problems with Calculate and DATESBETWEEN for Rolling calculation

v-chuncz-msft — Wed, 10 Nov 2021 09:13:17 GMT

Anonymous

For any time intelligence function, you could implement a custom DAX formula.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

To avoid circular dependency error, see post below as an example.

https://community.powerbi.com/t5/Desktop/Can-t-link-2-columns-one-of-which-is-calculated/m-p/351000#M157793