cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Percent Change over Previous Period? Must be dynamic for weeks, months, etc.

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.

1 ACCEPTED SOLUTION
Super User

@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])))
)``````

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

2 REPLIES 2
Helper V

@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!

Super User

@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])))
)``````

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors