Problems with Calculate and DATESBETWEEN for Rolling calculation

[Anonymous]

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 

[v-chuncz-msft]

@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

[Anonymous]

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

[VahidDM]

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!!

[Anonymous]

Thanks @VahidDM !! 

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

[VahidDM]

@Anonymous 

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

Appreciate your Kudos!!