Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |