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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MiKeZZa
Post Patron
Post Patron

Rolling average per day over months (not MTD)

I'm making a report based on the day of the month (1-31). I want to make a report that says how much expenses there have been in previous months per day and compare that to the current month. Lets say I have this:

 

1 Jan 10

1 Jan 20

2 Jan 20

1 Feb 10

2 Feb 20

2 Feb 30

1 Mar 15

2 Mar 20

2 Mar 20

 

So when it's 2 March now I want to calculate this is an report based:

Previous months:

1 = (10+20+10) / 3 = 13.33

2 = (20+20+30) / 3 = 23.33 + 13.33 (from day 1) = 36.66

 

Current month:

1 = 15

2 = (20+20) / 2 = 20 + 15 (from day 1) = 35

 

So the 'problem' is that I want to do something that is like a MTD, but then not just ín a month, but for all months for 1 specific date.

 

So it must turn it to this (and of course 31 days but that was a little to much work to create some dummydata for that Smiley Very Happy):

1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

Hi @MiKeZZa,

Based on my test, you could refer to below steps:

Create four measures:

Previous = CALCULATE(AVERAGE(Table1[Value]),FILTER(ALL(Table1),'Table1'[Item]=MAX('Table1'[Item])&&'Table1'[Month]<>"Mar"))
Previous running total in Item = CALCULATE(SUMX(FILTER(ALL(Table1[Item]),Table1[Item]<=MAX(Table1[Item])),'Table1'[Previous]))
Current = CALCULATE(AVERAGE(Table1[Value]),FILTER(ALL(Table1),'Table1'[Item]=MAX('Table1'[Item])&&'Table1'[Month]="Mar"))
Current running total in Item = CALCULATE(SUMX(FILTER(ALL(Table1[Item]),Table1[Item]<=MAX(Table1[Item])),'Table1'[Current]))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-danhe-msft
Employee
Employee

Hi @MiKeZZa,

Based on my test, you could refer to below steps:

Create four measures:

Previous = CALCULATE(AVERAGE(Table1[Value]),FILTER(ALL(Table1),'Table1'[Item]=MAX('Table1'[Item])&&'Table1'[Month]<>"Mar"))
Previous running total in Item = CALCULATE(SUMX(FILTER(ALL(Table1[Item]),Table1[Item]<=MAX(Table1[Item])),'Table1'[Previous]))
Current = CALCULATE(AVERAGE(Table1[Value]),FILTER(ALL(Table1),'Table1'[Item]=MAX('Table1'[Item])&&'Table1'[Month]="Mar"))
Current running total in Item = CALCULATE(SUMX(FILTER(ALL(Table1[Item]),Table1[Item]<=MAX(Table1[Item])),'Table1'[Current]))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yeah. This works great. I had to make some little changes to get it done at my PBIX (off course) but works great. Thank you!

 

One additional question; is it possible to cut off the current running total in item so that it stops growing after the current date? What I have now (because of that it's the 19th today) is this:

 

1 - 10

2 - 25

3 - 35

4 - 47

5 - 50

6 - 62

7 - 85

8 - 100

9 - 102

10 - 105

11 - 122

12 - 125

13 - 135

14 - 147

15 - 152

16 - 155

17 - 158

18 - 165

19 - 165

20 - 165

.....

31 - 165

 

Off course this is the normal behaviour for a running total, but for this case it's more nice that it stops at the current day. You also have an idea how this can be done?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.