Date offset using Direct Query measures

[wynhodgkiss]

I am working with a composite model consisting of a live connection to a PBI dataset & data in excel. In the PBI dataset there is a single measure "Sales" which then has numerous dimensions controlling it including measure type (£, units, etc), time aggregation (month, ytd, rolling quarter etc). The other dimensions required are month & territory.

What I need to create is a measure for sales per territory but with an offset, something like this:

Offset_Sales =  IF ( OR ( RepTable[T] = 1, RepTable[T] = 9),[PrevMonthSales],[CurrentMonthSales])

where PrevMonthSales is 

PrevMonth_Sales = CALCULATE ([Sales],PARALLELPERIOD(MonthTable[Month],-1,MONTH))

but that's not working for me in various permutations.

What I'm looking to create is below:

Any help would be much appreciated.

[wynhodgkiss]

Perfect, thanks for your help

[amitchandak]

@wynhodgkiss , You need to have a date table to make it work. You can create that in power bi or at source.

PrevMonth_Sales = CALCULATE ([Sales],PARALLELPERIOD(Date[Date],-1,MONTH))

You need use the same date table in visual, measure and slicers/filters

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[wynhodgkiss]

Hi, thanks for your reply but it's the offset that I'm struggling with not the previous or current month sales. I do have a dates table as suggested. The formula I am trying to make work is:

Offset_Sales =  IF ( OR ( RepTable[T] = 1, RepTable[T] = 9),[PrevMonthSales],[CurrentMonthSales])

[v-zhangti · Accepted solution]

Hi, @wynhodgkiss 

You can try the following methods.

Offset_Sales = IF ( OR ( SELECTEDVALUE(RepTable[T])= 1, SELECTEDVALUE(RepTable[T]) = 9),[PrevMonthSales],[CurrentMonthSales])

OR 

Measure = IF ( SELECTEDVALUE(RepTable[T]) in {1,9} ,[PrevMonthSales],[CurrentMonthSales])

Both of these formulas work.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.