Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Looking to write measure that compares the avg sales from the past 3 months to the avg sales from the previous 3 months. This first piece is easy, but i'm struggling with calculating between 3 months ago and 6 months ago. Is there an easy why to do this?
Heres my current Dax, which works great. Problem is its taking the last 3 months avg and dividing by the last 6 months avg. Again, I need to somehow get it to only look at the 3-6 months ago range.
You cannot use time-intel functions with any table you want. This does not work like that. Please read carefully the documentation on DATESINPERIOD. The whole of it.
Then, please go to this place and read it.
Then, once you know that you have to have a proper calendar in your model, you can write this:
// Please bear in mind that this code
// will not work correctly if you use
// the time-intel function on a non-calendar
// table.
Trend =
var LastVisibleDate = MAX( 'Calendar'[Date] )
var _3MonthPeriod =
DATESINPERIOD(
'Calendar'[Date],
LastVisibleDate,
-3,
MONTH
)
var Between6and3MonthPeriod =
EXCEPT(
DATESINPERIOD(
'Calendar'[Date],
LastVisibleDate,
-6,
MONTH
),
_3MonthPeriod
)
VAR three =
CALCULATE(
[Sales] / 3,
_3MonthPeriod
)
VAR six =
CALCULATE(
[Sales] / 3,
Between6and3MonthPeriod
)
RETURN
DIVIDE(
three,
six
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |