cancel
Showing results for
Did you mean:
Regular Visitor

Rolling 12 and All() together acting in unexpected way (V2)

I have a very simple table, that I'm trying to do the following:

For each given month, find the sum of the last 12 month for each person plus sum up all(persons) who that particular month have a Flag = Yes.  Put another way:  the rolling 12 of all people who have a flag = "yes" that month.

I failed with this formula

```Rolling for all =
CALCULATE (
[Sum Value],
ALL ( 'Table'[Person] ),
DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -12, MONTH ),
'Table'[Flag] = "Yes"
)```

Here is the behavior I want:

I have a value of 1,000 for each month for each person in this test.  So, for each individual, the rolling 12 should be 12,000.

If in a given month, 5 people have a flag of 'yes', then I should have a value of 60,000 total - regardless of what flags they had in the last 12 months.

What actually happens:

The formula looks at the past 12 months and only includes a given month IF the flag = "yes".  So, someone with that flag for only 3 of the last 12 months would only contribute 3,000 to total.  I can see why it does that, but I don't want that.  I want the full 12 months rolling if the current month flag = "yes", irrespective of what the last 12 months had for a flag.

Put another way:  if there are 4 people with the flag = yes then it should show 48,000.  Then if the very next month 1 more person gets the flag = yes (5 total), then that same month it should jump to 60,000.  Instead, it jumps to 49,000. (For this test, I have 1,000 per person per month going back in time)

 Person Date Value Flag C October 2019 1000 Yes E October 2019 1000 Yes D October 2019 1000 Yes B October 2019 1000 Yes A October 2019 1000 Yes E September 2019 1000 Yes D September 2019 1000 Yes C September 2019 1000 Yes B September 2019 1000 Yes A September 2019 1000 Yes C August 2019 1000 Yes E August 2019 1000 Yes D August 2019 1000 Yes B August 2019 1000 Yes A August 2019 1000 Yes E July 2019 1000 Yes D July 2019 1000 Yes C July 2019 1000 Yes B July 2019 1000 Yes A July 2019 1000 Yes E June 2019 1000 D June 2019 1000 Yes C June 2019 1000 Yes B June 2019 1000 Yes A June 2019 1000 Yes C May 2019 1000 Yes E May 2019 1000 D May 2019 1000 Yes B May 2019 1000 Yes A May 2019 1000 Yes D April 2019 1000 Yes C April 2019 1000 Yes E April 2019 1000 B April 2019 1000 Yes A April 2019 1000 Yes E March 2019 1000 D March 2019 1000 Yes C March 2019 1000 Yes B March 2019 1000 Yes A March 2019 1000 Yes C February 2019 1000 Yes E February 2019 1000 D February 2019 1000 Yes B February 2019 1000 Yes A February 2019 1000 Yes E January 2019 1000 D January 2019 1000 Yes C January 2019 1000 Yes B January 2019 1000 Yes A January 2019 1000 Yes E December 2018 1000 D December 2018 1000 Yes C December 2018 1000 Yes B December 2018 1000 Yes A December 2018 1000 Yes E November 2018 1000 D November 2018 1000 Yes C November 2018 1000 Yes B November 2018 1000 Yes A November 2018 1000 Yes D October 2018 1000 Yes C October 2018 1000 Yes E October 2018 1000 B October 2018 1000 Yes A October 2018 1000 Yes E September 2018 1000 D September 2018 1000 Yes C September 2018 1000 Yes B September 2018 1000 Yes A September 2018 1000 Yes C August 2018 1000 Yes E August 2018 1000 D August 2018 1000 Yes B August 2018 1000 Yes A August 2018 1000 Yes E July 2018 1000 D July 2018 1000 Yes C July 2018 1000 Yes B July 2018 1000 Yes A July 2018 1000 Yes E June 2018 1000 D June 2018 1000 Yes C June 2018 1000 Yes B June 2018 1000 Yes A June 2018 1000 Yes E May 2018 1000 D May 2018 1000 Yes C May 2018 1000 Yes B May 2018 1000 Yes A May 2018 1000 Yes E April 2018 1000 D April 2018 1000 Yes C April 2018 1000 Yes B April 2018 1000 Yes A April 2018 1000 Yes E March 2018 1000 D March 2018 1000 Yes C March 2018 1000 Yes B March 2018 1000 Yes A March 2018 1000 Yes E February 2018 1000 D February 2018 1000 Yes C February 2018 1000 Yes B February 2018 1000 Yes A February 2018 1000 Yes E January 2018 1000 D January 2018 1000 Yes C January 2018 1000 Yes B January 2018 1000 Yes A January 2018 1000 Yes

https://1drv.ms/u/s!AlCaI3WpECWQgbsXLUxAXeUrQpSAcQ?e=nXMrDK

Regular Visitor

Re: Rolling 12 and All() together acting in unexpected way (V2)

Okay, thinking outside the box here.

One way to look at it is I want the Rolling 12 for any one individual to be fixed for each row.  Could I put the rolling 12 for each individual in a calculated column or have it generated by Power Query instead?  Then it would be static and unchanging when I filter by flag?

When I try to create a calculated column with Datesinrange, it error messages that I have a circular reference.  Any other ways to do it?

Don't even know if it's possible to calculate a rolling 12 in Power Query?

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,123)