4 Weeks Rolling Average - Power BI

Hey guys!

In this post we will see how we can do a 4 weeks rolling average. In another post (Here) I have elaborated what a rolling average is and how it's easy to do it by Year, month and quarter. But since power bi doesn't have week level I will show you a workaround in this article. 

Also the major challenge with using the quick measure for rolling average is that you can't use the data without the date hierarchy but with this approach we can use date in any format we need easily. 

Problem Statement: 

Get the 4 weeks rolling average of resolution time. (support date)

Sample data:

I prepared a sample data like this from 01-Jan-2020 to 07-april-2021 with =RandBetween(0.1,100)/57 function in column C to get some randome decimal values as resolution time

Solution:

While loading the data

 1. I created an extra column

WeekStart=Date.StartOfWeek([Created Date],Day.Sunday)

This column results in the start date of the week for the created date. Change the type to Date in Transform->DataType: Date

2. Created another column with the following formula to get the start date 28 days (4 weeks ago)

4weeksagoWeekStart =Date.StartOfWeek(Date.AddDays([Created Date],-28),Day.Sunday)

In this example we are taking 4 weeks rolling average with last completed week. In case you have a use case for 3 weeks change the -28 to -21 in the formula above.

 Load the data.

After loading:

Create the following measures:

1. Average Resolution time is the avg of the column you want to create. In case you want to apply filter on the dataset for the average like exclude all cases with status=cancelled you can use CALCULATE(avg,filter)

avgResolutionTime = AVERAGE(Sheet1[Resolution Time])

2. Running Total of the average. To create this use the following formula 

Calculate( avgmeasurecreatedin1,Filter(all(Table),weekstartdate<=max(weekstartdate) && weekstartdate<dateadd(weekstartdate,-28,days)

This calculates the avg of last 4 weeks and puts it against the start of the next week. 

avgResolutionTime running total in WeekStart =

CALCULATE(

    [avgResolutionTime],

    FILTER(

        all(Sheet1),

    Sheet1[Created Date]>=max(Sheet1[4weeksagoWeekStart]) && Sheet1[Created Date]<max(Sheet1[WeekStart])

    )

)

In this example we consider last completed week for every week start. If you want to include the current week create a next week start while loading with adddays(Date,+7,days) so you get the start of next week. Also create 3 weeks ago start by using -21 instead of -28. And Change the above formula to Sheet1[Created Date]>=max(Sheet1[3weeksagoWeekStart]) && Sheet1[Created Date]<max(Sheet1[NExtWeekStart])

Explaining the above formula

Lets take an example to understand the formula. If we want the 4 weeks rolling average on 31st  of May 2020 (Sunday) the rolling average must be for the dates between 03 May (sunday) to 30 May saturday. 

Week 1 May 03- May 09

Week 2 May 10 to May 16

Week 3 May 17 to May 23

Week 4 May 24 to May 30

So against 31-May-2020 we need to have Avg(ResolutionTime) from May 03-May 30

For 31-May-2020 

max(Sheet1[4weeksagoWeekStart]) is May 03

max(Sheet1[WeekStart])) is May 31

So we are averaging resolutiontime in this time range which results in rolling 4 weeks average. 

Screenshot of the 4 weeks in consideration for value against 31st May. Average is 0.99

Screenshot of rolling average calculated with the above formula: Value =0.99 

A thumbs up, if this works, motivates me to write more on this blog. Do follow to get notified when i post new tips and tricks. 

 

Row level Security (RLS)

  Hey There! 

In this article I will be explaining row level security in detail. 

Firstly, what is row level security? When you have a dataset where not all groups can view all the data, we need some kind of security to show only that data that's appropriate for viewing by each group. And we have to do it based on who has logged in. Since the security is applied at row level (only some rows of data are available for a certain group) they call it row level security.

Use case:

Report that shows the claims of the employees. But the Geo leads can only see the claims from their regions. 

Solution:

IN PBIX:

Setting up the data:

1. Load the data from the file shown above. 

2. Load another inline mapping table to map the geo leads with their respective geos. (alternatively, you can maintain this data in another excel file outside power bi. Advantage of this approach is that for changes with the geo leads, you dont have to open pbix and alter the file)

2.a. To load this geo leads table click on Enter data. 

2.b. Create a column for Geo and one for the leads. You can either give the lead email id or the name with domain like domain\username. But the next step will be dependent on what you give here. For my example I am taking emailid.

2.c. Click OK. Close and apply. 

3. Create relationship between the two tables with both as the direction.

4. Create a measure as follows in the claim sheet table

Currentuser = USERPRINCIPALNAME()

In step 2 if you had chosen to use name in the created table the measure has to be Currentuser = USERNAME()

5. Check if the value of current user is right by using this measure in a table. You must see the logged in user email id (we are taking Currentuser = USERPRINCIPALNAME() for this example) 

Managing Users:

1. Click on Modeling and Manage user

2. Click create and give a name for the user group: I am giving Geo Leads as the name. Click on the Mapping Table and type the DAX in the right pane as  

                    

Lead=[Currentuser]

This will filter the Lead column from the mapping table with the logged in user. thereby filtering the original claim data for the geo assocaited. 

3. Create another user group called "Super User" (name anything you want) and dont give any dax expression. This helps you manage a super user group, can be userself for data validation, who can view all regions without any filter. 

4. Test the roles by clicking on view as and giving the test email ids.

loaded data for reference:

click ok to see the following filtered result

Now that everything is working fine, publish the file. 

Cloud Service:

1. Navigate to Data sets +data flows. Click on the 3 dots near the data set associated with the file we just published. Select security.

2. Add people in their groups. For example the ones listed as geo leads in our table need to be listed undder geo leads for the RLS to work. 

3. To test it, click on the  three dots and test as a role. Enter the email id you want to test the RLS for. 

If this works, we have done all the steps right. 

Hope this article helped you. Please leave a thumbs up below if it did. :)

Loading multiple files from a folder

   Hey there! 

In this article I will show you how you can load multiple excel files from a folder. This is not incremental load as such but this approach picks up data as and when a new file is uploaded.

Exampe use case: 
A team generally reviews orders of the week in an excel, add comments and uploads in a sharepoint location. 
Files are named as "Book Week X" (Book Week 1, Book week 2)

Power bi requirement: To visualize the values in all these files and pick up new files that are being uploaded every week. 

Solution:

Here I have filtered the files that start with the word "Book". 

If you are not sure how to use advanced editor to do this:

1. Click the filter drop down for column named "Name".

2. Select one of the file names

3. You will have a Filtered Rows step generated at the right pane under "Applied steps"

4. Click on it and look at the formula bar.  Say it has : [Name]="Book1.xlsx". Edit it to Text.Contains formula shown below.

Click on combine and select combine files from the drop down. 

Now select one sample file, load the right sheet. The same sheet will be loaded from all files existing in the folder (that were the result of the previous filter step). As the dataset is refreshed, the new files that match the filter query (that is the files in the folder with the word "Book"), the sheet 1 of these files will be loaded. 

A few steps will get auto added. Click on the unique identifier column and remove duplicates to avoid duplication due to uploading same file twice by mistake in the folder.

The final output will have a column Source.Name auto added with the file from which the row is added. 

Let me know in comments if this article was useful.