Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Greetings. I have a measure (we'll call it MyMeasure), and a typical looking Dates table like so:
My report uses MyMeasure in Tables and Line Charts with a time value on the X axis. So here's some examples of how the measure might be applied -- we see months in the Table, weeks as the Line Chart X axis.
Quite simply, what I want is a new complementary measure that will show the percent change over the previous period regardess of the period being used. So it should be dynamic and adjust to whatever date bin is in use on the visual -- could be years, months, quarters, weeks or days. Something like this:
Can anyone help? I have a few ideas, but they're very inefficient or just verbose. Feel like there's a more elegant why. Thanks.
Solved! Go to Solution.
@bvy , with offset and isinscope you can have meausre like
Previouse Value = SWITCH(TRUE(),
ISINSCOPE('Date'[Date]), CALCULATE([Net], OFFSET(-1, ALL('Date'[Date]))),
ISINSCOPE('Date'[Month Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year],'Date'[Year],'Date'[Qtr Year]),ORDERBY('Date'[Month Year sort]))),
ISINSCOPE('Date'[Qtr Year]),CALCULATE([Net], OFFSET(-1, ALL('Date'[Year], 'Date'[Qtr Year]))),
ISINSCOPE('Date'[Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Year])))
)
example: https://medium.com/microsoft-power-bi/power-bi-offset-function-84177cc2cafd
Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U
The same can be done using the Time intelligence function
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
@amitchandak Thank you. That works really well. I had some issues with the OFFSET function working differently in the service vs. Desktop when it was first introduced, but it seems more solid now, and a good tool to use in this case. Thanks again!
@bvy , with offset and isinscope you can have meausre like
Previouse Value = SWITCH(TRUE(),
ISINSCOPE('Date'[Date]), CALCULATE([Net], OFFSET(-1, ALL('Date'[Date]))),
ISINSCOPE('Date'[Month Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Month Year sort],'Date'[Month Year],'Date'[Year],'Date'[Qtr Year]),ORDERBY('Date'[Month Year sort]))),
ISINSCOPE('Date'[Qtr Year]),CALCULATE([Net], OFFSET(-1, ALL('Date'[Year], 'Date'[Qtr Year]))),
ISINSCOPE('Date'[Year]), CALCULATE([Net],OFFSET(-1, ALL('Date'[Year])))
)
example: https://medium.com/microsoft-power-bi/power-bi-offset-function-84177cc2cafd
Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U
The same can be done using the Time intelligence function
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
94 | |
82 | |
70 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |