Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Problems with Calculate and DATESBETWEEN for Rolling calculation

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 

5 REPLIES 5
v-chuncz-msft
Community Support
Community Support

@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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

VahidDM
Super User
Super User

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
Not applicable

Thanks @VahidDM !! 

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

@Anonymous 

 

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

 

Appreciate your Kudos!!

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors