Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
bvy
Helper V
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: 

 

bvy_0-1713915166056.png

 

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. 

 

bvy_1-1713915380330.png

 

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: 

 

bvy_2-1713916403985.png

 

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
amitchandak
Super User
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])))
)

 

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

View solution in original post

2 REPLIES 2
bvy
Helper V
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! 

amitchandak
Super User
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])))
)

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.