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.
Okay. I think this should be fairly easy to do, but I'm coming up blank. I have a column of IDs and a column for the Fiscal Year for a transaction that occurred.
I want to be able to count the number of IDs for this year (works fine), but I want to create a measure that counts the number of IDs for the next year (but to show that next year number on the current year).
So the table should look like:
Year | This year | NextYear |
2010 | 1589 | 1647 |
2011 | 1647 | 1981 |
2012 | 1981 | 1967 |
2013 | 1967 | 2085 |
2014 | 2085 |
With the idea that I can do further analysis once I have the "Next Year" measure working properly.
I've tried:
NextYear_count = CALCULATE(DISTINCTCOUNT(Transaction[ID]),FILTER(Transaction,Transaction[FiscalYear]= Transaction[FiscalYear]+1))
I realize that in the row context, "year" will never equal "year + 1" (which is why I've come up blank), but I do not quite understand how to ignore the "year" row context without ignoring it all together since I need it for the calculation as a whole.
Any advice would be appreciated.
Thanks!
Jonathan
Solved! Go to Solution.
Hi @Anonymous
Try this one
NextYear_count = CALCULATE ( DISTINCTCOUNT ( Transaction[ID] ), FILTER ( ALLSELECTED ( Transaction ), Transaction[FiscalYear] = SELECTEDVALUE ( Transaction[FiscalYear] ) + 1 ) )
Hi @Anonymous
Try this one
NextYear_count = CALCULATE ( DISTINCTCOUNT ( Transaction[ID] ), FILTER ( ALLSELECTED ( Transaction ), Transaction[FiscalYear] = SELECTEDVALUE ( Transaction[FiscalYear] ) + 1 ) )
Try this, also shared in my blog post on the subject:
https://sharepointlibrarian.com/2018/03/05/power-bi-calculate-next-years-amount-in-previous-years-ro...
NextYearCount = var next= CALCULATE( FIRSTNONBLANK('Transaction'[This year],""), FILTER('Transaction','Transaction'[Year]>EARLIER('Transaction'[Year]) && 'Transaction'[Year]=EARLIER('Transaction'[NextYear]))) return IF(ISBLANK(next),BLANK(), next)
You'll just need to be sure you have a "NextYear" column which is calculated as [Year]+1 and that your [Year] column is in number format.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |