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 |
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?
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
User | Count |
---|---|
111 | |
73 | |
49 | |
33 | |
33 |
User | Count |
---|---|
149 | |
95 | |
86 | |
50 | |
41 |