Calculate measure between relative dates

Anonymous — Tue, 25 May 2021 17:53:45 GMT

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.

Trend =

var three = CALCULATE([Sales],DATESINPERIOD(Orders[date],LASTDATE(Orders[date]),-3,MONTH))/3

var six = CALCULATE([Sales],DATESINPERIOD(Orders[date],LASTDATE(Orders[date]),-6,MONTH))/3

return

divide(three,six)

Re: Calculate measure between relative dates

daxer-almighty — Tue, 25 May 2021 22:15:46 GMT

Anonymous

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 )

topic Re: Calculate measure between relative dates in DAX Commands and Tips

Calculate measure between relative dates

Re: Calculate measure between relative dates