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

 

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
Helper I
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?

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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