cancel
Showing results for
Did you mean:
Helper I

## 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

Helper I

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

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors