cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jschlereth Member
Member

Calculate count for next year based on this year

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:

 

YearThis yearNextYear
201015891647
201116471981
201219811967
201319672085
20142085 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate count for next year based on this year

Hi @jschlereth

 

Try this one

 

NextYear_count =
CALCULATE (
    DISTINCTCOUNT ( Transaction[ID] ),
    FILTER (
        ALLSELECTED ( Transaction ),
        Transaction[FiscalYear]
            = SELECTEDVALUE ( Transaction[FiscalYear] ) + 1
    )
)

View solution in original post

3 REPLIES 3
nchambe Regular Visitor
Regular Visitor

Re: Calculate count for next year based on this year

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.

Super User
Super User

Re: Calculate count for next year based on this year

Hi @jschlereth

 

Try this one

 

NextYear_count =
CALCULATE (
    DISTINCTCOUNT ( Transaction[ID] ),
    FILTER (
        ALLSELECTED ( Transaction ),
        Transaction[FiscalYear]
            = SELECTEDVALUE ( Transaction[FiscalYear] ) + 1
    )
)

View solution in original post

jschlereth Member
Member

Re: Calculate count for next year based on this year

@Zubair_Muhammad,

Thanks. That worked.

 

Jonathan 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 341 members 3,304 guests
Please welcome our newest community members: