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've summarized data into a simple table, that I'm trying to do the following: Table and attempted link to pbix file below.
For a given month, find the sum of the last 12 months, for all(persons), who that month particular month have a Flag = Yes.
I use 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.
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 |
https://1drv.ms/u/s!AlCaI3WpECWQgbsXLUxAXeUrQpSAcQ
Hi,
different ways of doing this, here is one method:
First create a new column called YearMonthNum like this
YearMonthNum = ( YEAR ( 'Table'[Date] ) - 2010 ) * 12 + MONTH ( 'Table'[Date] )
which generates a sequential number of month and year, starting with 1 at january 2010. 2010 is somewhat arbitrary chosen. The beauty of such a column is that you can just add or subtract a number change a period.
Then change your measure to this:
Rolling for all = VAR _tab = CALCULATETABLE ( VALUES ( 'Table'[Person] ); FILTER ( ALL ( 'Table' ); 'Table'[Flag] = "Yes" && 'Table'[YearMonthNum] <= MIN ( 'Table'[YearMonthNum] ) && 'Table'[YearMonthNum] >= MIN ( 'Table'[YearMonthNum] ) - 11 ) ) RETURN CALCULATE ( [Sum Value]; FILTER ( ALL ( 'Table' ); 'Table'[Person] IN _tab && 'Table'[YearMonthNum] <= MIN ( 'Table'[YearMonthNum] ) && 'Table'[YearMonthNum] >= MIN ( 'Table'[YearMonthNum] ) - 11 ) )
where the variable finds all Persons with "Yes" in the last 12 months, and then main part looks at all those and sums over the last 12 months.
cheers,
Sturla
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |