Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've found that I can use the following to identify a rolling prior 1 month period:
Present Month Volume = CALCULATE(SUM('Totals'[USD Total Amount]),
DATESBETWEEN('Totals'[Trade Date],
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1),
EOMONTH(today(),-1)))
The issue is that my data runs not from today as indicated in the formula, but from a random final date, usually the last day of the prior month. How should I adapt the formula to deliver the prior month's result?
Solved! Go to Solution.
my data runs not from today as indicated in the formula, but from a random final date, usually the last day of the prior month.
What exact result are you expecting? Is the “prior month’s result” refers to the results from “today” subtracting one month? (So the final day is “today”).
As I tested, I have got a trade date column like:
Referring to your DAX, the final result becomes (the final day is 2017-12-31)
So I assume it is not the result set you want. (screenshot above)
Hence, as a quick guess, I suggest you replace the
DATESBETWEEN('Totals'[Trade Date],
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1),
EOMONTH(today(),-1)))
With
DATESINPERIOD('Totals'[Trade Date], TODAY(), -1, MONTH)
Then I get result ended up “today()”
Regards,
Charlie Liao
my data runs not from today as indicated in the formula, but from a random final date, usually the last day of the prior month.
What exact result are you expecting? Is the “prior month’s result” refers to the results from “today” subtracting one month? (So the final day is “today”).
As I tested, I have got a trade date column like:
Referring to your DAX, the final result becomes (the final day is 2017-12-31)
So I assume it is not the result set you want. (screenshot above)
Hence, as a quick guess, I suggest you replace the
DATESBETWEEN('Totals'[Trade Date],
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1),
EOMONTH(today(),-1)))
With
DATESINPERIOD('Totals'[Trade Date], TODAY(), -1, MONTH)
Then I get result ended up “today()”
Regards,
Charlie Liao
Many thanks Charlie, that works.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |