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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
N_R_000
Helper I
Helper I

Monthly percentage change from a fixed point in time, based on a Slicer Selected month

I'd like to create something like the below, which shows the % change of something when compared to a single point in time. 

I know I can easily show 'month on month' % changes, but I'd like to show how the % changes look every month when that month is compared to a selected month in the past.  

0HVADyJv6S9-d0uM5_hOkosmzzjsNxJTcTqq9NKCK28.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I've had to sanitise the data a bit, but here's what I've got - a date table and an events table. Many events happen each day, so I'll need a COUNT for the 'Base Month' then a measure to take each subsequent month and compare to the Base Month to get the % change. I'd like to lines on the chart, one for each 'Event Type'.  The X axis will be 'Start of Month', the other axis will be '% Change'. 

 

I'd really like the 'Base Month' to be set by a slicer so the user can update this, but if that's not possible, I'm happy to hard code a date in DAX as plan b.  

 

[Dates]   [Events]  
DateFirstOfMonth  EventIDDateEventType
01/01/202301/01/2023  101/01/2023Minor
02/01/202301/01/2023  201/01/2023Major
03/01/202301/01/2023  301/01/2023Minor
04/01/202301/01/2023  405/01/2023Major
05/01/202301/01/2023  505/01/2023Minor
06/01/202301/01/2023  605/01/2023Major
07/01/202301/01/2023  705/01/2023Major
08/01/202301/01/2023  809/01/2023Minor
09/01/202301/01/2023  909/01/2023Major
10/01/202301/01/2023  1009/01/2023Major
11/01/202301/01/2023  1112/01/2023Minor
12/01/202301/01/2023  1212/01/2023Minor

These dates and events continue up to today, I've just truncated the data for this post. 

 

I've spent a day trying to get ChatGPT to help with DAX with no luck, so here's hoping the human commnuity can come to my aid! 

 

Thanks. 

1 REPLY 1
lbendlin
Super User
Super User

Please indicate the expected outcome based on your sample data - or provide more covering sample data.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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