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 =
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)
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?