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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

% change of events for a user month to month

Hi all,

Still new to Power BI so if it's a silly question please forgive me (I also tried searching but given my limited vocab around this I didn't find any answers).

 

I've been tasked with identifying users who are increasing or decreasing their usage (event) on our platform from month to month. So I need to be able to look at the table and compare all events from one month to another by user and identify which have the great month-to-month increase or decrease.

 

Here is a copy of some data that I have sanitised. Dummy Data

 

Cheers

Dave

6 REPLIES 6
Shivendra
Frequent Visitor

Hi Dave,

 

It’s not the easy one. Please follow the below steps. 

 

01. Go to Query Editor.

02. Create a reference query of your existing one (Optional).

03. Create YearMonth column.

04. Create a Group using above column and add index for each YearMonth value (starting with 1) named CurrentRow. it will give an effect of creating row_number with partition by each month.

05. Expand required rows.

06. Again, Create a Group using above column and add index for each YearMonth value (starting with 0) named PreviousRow. it will give an effect of creating row_number with partition by each month.

07. Expand required rows.

08. Merge Step 05 and 07 on the basis of CurrentRow and PreviousRow it will give previous and current value side by side.

09. You can do easy manipulation to calculate increase and decrease. 

 

Shivendra

SQLDig.com

Anonymous
Not applicable

Hi Shivendra,

 

I got to step 6 ok, but I can't figure out how to expand the required rows. Tried searching but couldn't find an answer.

 

Cheers,
Dave Hynes,

Hi Dave,

 

Img01.JPG

 

 

 

 

 

Click on Red circled icon you will get list of columns to show. 

 

 

 

Shivendra

SQLDig.Com

Anonymous
Not applicable

The PreviousRow and CurrentRow groups don't appear in the query editor, I must've missed something.

Anonymous
Not applicable

Thanks, Shivendra I'll give it a go and let you know how I get on.

 

I'm glad it's hard, means I'm not completely useless! 😛

Anonymous
Not applicable

Hi Dave

 

The data you have shared is fact data.

Please create a calendar dimension with the month column. Join the fact - event date column with the date column in the calendar table. Create measures that identify usage and then increase/decrease in usage month to month and then plot the measure against the month column from the calendar table. You may need more dimensions based on requirements.

 

Please let me know if this helps.

 

regards,

Cmaz

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors