Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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.
Perfect, thanks for your help
@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.
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])
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
54 | |
54 | |
37 | |
29 |
User | Count |
---|---|
78 | |
64 | |
45 | |
40 | |
40 |