% change of events for a user month to month

[Anonymous]

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

[Shivendra]

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]

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]

Hi Dave,

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

Shivendra

SQLDig.Com

[Anonymous]

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

[Anonymous]

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]

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