Rolling 12 and All() together acting in unexpected way

[jjkmd]

I've summarized data into a simple table, that I'm trying to do the following:  Table and attempted link to pbix file below.

For a given month, find the sum of the last 12 months, for all(persons), who that month particular month have a Flag = Yes.

I use 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.

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

pbix attempt at sharing

[sturlaws]

Hi,

different ways of doing this, here is one method:

First create a new column called YearMonthNum like this

YearMonthNum =
 ( YEAR ( 'Table'[Date] ) - 2010 ) * 12
    + MONTH ( 'Table'[Date] )

which generates a sequential number of month and year, starting with 1 at january 2010. 2010 is somewhat arbitrary chosen. The beauty of such a column is that you can just add or subtract a number change a period.

Then change your measure to this:

Rolling for all =
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table'[Person] );
        FILTER (
            ALL ( 'Table' );
            'Table'[Flag] = "Yes"
                && 'Table'[YearMonthNum] <= MIN ( 'Table'[YearMonthNum] )
                && 'Table'[YearMonthNum]
                    >= MIN ( 'Table'[YearMonthNum] ) - 11
        )
    )
RETURN
    CALCULATE (
        [Sum Value];
        FILTER (
            ALL ( 'Table' );
            'Table'[Person] IN _tab
                && 'Table'[YearMonthNum] <= MIN ( 'Table'[YearMonthNum] )
                && 'Table'[YearMonthNum]
                    >= MIN ( 'Table'[YearMonthNum] ) - 11
        )
    )

where the variable finds all Persons with "Yes" in the last 12 months, and then main part looks at all those and sums over the last 12 months.

cheers,
Sturla