cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidhynes Frequent Visitor
Frequent Visitor

% 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
Highlighted
cmaz Regular Visitor
Regular Visitor

Re: % change of events for a user month to month

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

 

Shivendra Frequent Visitor
Frequent Visitor

Re: % change of events for a user month to month

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

davidhynes Frequent Visitor
Frequent Visitor

Re: % change of events for a user month to month

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! Smiley Tongue

davidhynes Frequent Visitor
Frequent Visitor

Re: % change of events for a user month to month

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,

Shivendra Frequent Visitor
Frequent Visitor

Re: % change of events for a user month to month

Hi Dave,

 

Img01.JPG

 

 

 

 

 

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

 

 

 

Shivendra

SQLDig.Com

davidhynes Frequent Visitor
Frequent Visitor

Re: % change of events for a user month to month

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 33 members 925 guests
Please welcome our newest community members: