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
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
@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.
It worked as a variable, thouhgh not in a row level. Will try some iterator functions!
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!!
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!!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |