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
Nimesh2kotadia
Regular Visitor

Rolling average for categories.

I have data in form of three columns (Date, Incidents and Department). I want to calculate a rolling average measure for a chart which has departments in rows, incident sum for each month and finally a rolling 6 month average value for that department. 

 

I have tried solution given on https://www.youtube.com/watch?v=ACvYaXnpyCM. It breaks when i try to add department to the rows instead of month names. 

6 REPLIES 6
Nimesh2kotadia
Regular Visitor

i want to display data in the following format - 

Department - April - March - February - January - 6M rolling aerage. 

 

When i use the already created measure in the above scenario, it produces one extra column for each month. Even values are not correctly calculated. Can you please help me understand why the measure fails. aaa.JPG

Do you have a date table or is it all coming from one table? If its all in one table then you'll need to create a proper date table. Plenty of articles and videos about that.

 It was all coming from a single table. But i can create a date table. How to proceed after creating the date table? 

After you've got the date table link that to your fact table and change the definitions of any measures to use the date table instead of the fact table. Also change the visuals to use columns from the date table. It should just work then.

As you suggested, i used a separate date table and made changes to the formula. It is still giving the same result. Why is it so hard to do simple things in DAX? As shown in below picture, i want to see last three months incidents department wise and 6 month moving average. Instead of total column i want to see RA6M. Can you please help me understand the solution? 

aaa.JPG

I created some dummy data in excel like below. 

Nimesh2kotadia_0-1684809353876.png

 

 

johnt75
Super User
Super User

If I understand correctly you want a visual which has departments on the rows and shows the average incidents over the last 6 months. Using the method from the video you can add a visual filter to the table so that the date is in the current month. that will then work out the average over the last 6 months.

Helpful resources

Announcements
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.

Top Kudoed Authors