cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jjkmd Regular Visitor
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)

 

Annotation 2019-10-13 174634.jpg

 

PersonDateValueFlag
COctober 20191000Yes
EOctober 20191000Yes
DOctober 20191000Yes
BOctober 20191000Yes
AOctober 20191000Yes
ESeptember 20191000Yes
DSeptember 20191000Yes
CSeptember 20191000Yes
BSeptember 20191000Yes
ASeptember 20191000Yes
CAugust 20191000Yes
EAugust 20191000Yes
DAugust 20191000Yes
BAugust 20191000Yes
AAugust 20191000Yes
EJuly 20191000Yes
DJuly 20191000Yes
CJuly 20191000Yes
BJuly 20191000Yes
AJuly 20191000Yes
EJune 20191000 
DJune 20191000Yes
CJune 20191000Yes
BJune 20191000Yes
AJune 20191000Yes
CMay 20191000Yes
EMay 20191000 
DMay 20191000Yes
BMay 20191000Yes
AMay 20191000Yes
DApril 20191000Yes
CApril 20191000Yes
EApril 20191000 
BApril 20191000Yes
AApril 20191000Yes
EMarch 20191000 
DMarch 20191000Yes
CMarch 20191000Yes
BMarch 20191000Yes
AMarch 20191000Yes
CFebruary 20191000Yes
EFebruary 20191000 
DFebruary 20191000Yes
BFebruary 20191000Yes
AFebruary 20191000Yes
EJanuary 20191000 
DJanuary 20191000Yes
CJanuary 20191000Yes
BJanuary 20191000Yes
AJanuary 20191000Yes
EDecember 20181000 
DDecember 20181000Yes
CDecember 20181000Yes
BDecember 20181000Yes
ADecember 20181000Yes
ENovember 20181000 
DNovember 20181000Yes
CNovember 20181000Yes
BNovember 20181000Yes
ANovember 20181000Yes
DOctober 20181000Yes
COctober 20181000Yes
EOctober 20181000 
BOctober 20181000Yes
AOctober 20181000Yes
ESeptember 20181000 
DSeptember 20181000Yes
CSeptember 20181000Yes
BSeptember 20181000Yes
ASeptember 20181000Yes
CAugust 20181000Yes
EAugust 20181000 
DAugust 20181000Yes
BAugust 20181000Yes
AAugust 20181000Yes
EJuly 20181000 
DJuly 20181000Yes
CJuly 20181000Yes
BJuly 20181000Yes
AJuly 20181000Yes
EJune 20181000 
DJune 20181000Yes
CJune 20181000Yes
BJune 20181000Yes
AJune 20181000Yes
EMay 20181000 
DMay 20181000Yes
CMay 20181000Yes
BMay 20181000Yes
AMay 20181000Yes
EApril 20181000 
DApril 20181000Yes
CApril 20181000Yes
BApril 20181000Yes
AApril 20181000Yes
EMarch 20181000 
DMarch 20181000Yes
CMarch 20181000Yes
BMarch 20181000Yes
AMarch 20181000Yes
EFebruary 20181000 
DFebruary 20181000Yes
CFebruary 20181000Yes
BFebruary 20181000Yes
AFebruary 20181000Yes
EJanuary 20181000 
DJanuary 20181000Yes
CJanuary 20181000Yes
BJanuary 20181000Yes
AJanuary 20181000Yes

 

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

1 REPLY 1
jjkmd Regular Visitor
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?

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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