Forum Discussion
Year Over Year Comparison with Offset
Hello all
I was recently tasked with looking into Power BI as a solution to our reporting needs, so I am just starting to do a bit of training on it.
As I am doing my training, I am trying to keep in mind what possible reports we may want to convert over into Power BI, one of these types of reports is a year over year comparison with an offset.
I know that there are some functions and visuals we can leverage such as [SAMEPERIODLASTYEAR]. And I have read that a Year over Year report isnt too out of the ordinary.
BUT I was wondering, is it possible to do a Year Over Year Comparison WITH an OFFSET? Specifically can this be done with a slicer?
So for example today is March 1, 2019.
My report would show data for March 1, 2019 vs March 1, 2018.
But I have some end users where they want to be able to shift last years data plus (+) or minus (-) a certain offset, or days.
So a user may want to have a report showing March 1, 2019 vs Feb 27, 2019. So a user would want to OFFSET last years day by -2 days. This would depend on the user input and cant be pre set.
We currently have reports (we use Oracle Business Intelligence Enterprise Edition [OBIEE]) that use parameters using a date and then an offset as shown below
Im just starting to get my feet wet and learn more about PBI, but I would greatly appreciate any advice/insight or if someone can point me in the right direction as to how I can get this kind of functionality.
2 Replies
- Greg_Deckler
Community Champion
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008- rodneyc8063_1
Helper V
That report is quite slick
I do appreciate the suggestion, and I think I would need more time to dig into that report. It is promising as it does a year over year compare, but then I still have a need for an "offset" on the date.
Currently we have the offset as a textbox variable that a user can enter.
It does not seem like PBI has an option to be able to use a textbox input, so I was debating possibly making a slicer with pre set values to serve as our offset. So instead of typing something, a user would be forced to select something.
Then from there pull in the offset value into our last year column.
I actually found this earlier post here that suggests I can use a selected value from a slicer in a DAX formula
Not sure if this would work as I expect?