Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more

Reply
Anonymous
Not applicable

Carry over previous year + accumulation current year

‎02-18-2022 07:05 AM

Hello,

 

I'm having some trouble of how to add the carryover, of the number of received applications in the previous year with study start in 2022, to the accumulation of received application YTD with study start in 2022. 

 

At the moment I have succeeded in producing a graph for the accumulation across the whole timespan, but I can't seem to figure out how to add the number of application received in the previous year as a carry over from the previous year, such that my graph maps the [carryover]+[accumulation 2020] i.e. the carryover is the cutting of the y axis. 

 

This is my current graph

Mercedes_b_0-1645195956077.png

Measure accumulation:

AccumOfAplications2022 = CALCULATE(Final_data[NumberOfApplications],FILTER(ALLSELECTED(Final_data[DateOfApplication]),Final_data[DateOfApplication]<=max(Final_data[DateOfApplication])),Final_data[Year of StudyStart2]=2022)
 
Measure Number of applications:
NumberOfApplications = COUNTROWS(Final_data)
 
This is a data sample:
 
DateOfApplicationStudystartdateReceived Week Number2Year of Application2Year of StudyStart2
05-02-202204-08-2022520222022
05-02-202204-08-2022520222022
05-02-202204-08-2022520222022
02-02-202204-08-2022520222022
31-01-202204-08-2022520222022
16-12-202131-07-20225020212022
26-11-202110-01-20224720212022
25-11-202110-01-20224720212022
25-11-202110-01-20224720212022
25-11-202110-01-20224720212022
22-11-202131-07-20224720212022
22-11-202110-01-20224720212022
22-11-202110-01-20224720212022
17-11-202110-01-20224620212022
15-11-202110-01-20224620212022
14-11-202110-01-20224520212022
09-11-202110-01-20224520212022
02-11-202110-01-20224420212022
31-10-202110-01-20224320212022
29-10-202110-01-20224320212022
29-10-202110-01-20224320212022
25-10-202110-01-20224320212022
25-10-202110-01-20224320212022
25-10-202110-01-20224320212022
09-10-202110-01-20224020212022
08-10-202110-01-20224020212022
04-10-202110-01-20224020212022
02-10-202110-01-20223920212022
02-10-202110-01-20223920212022
15-09-202131-07-20223720212022
09-09-202110-01-20223620212022
23-08-202110-01-20223420212022
18-08-202110-01-20223320212022
17-08-202110-01-20223320212022
17-08-202110-01-20223320212022
06-08-202131-07-20223120212022
04-07-202110-01-20222620212022
28-05-202110-01-20222120212022
26-05-202110-01-20222120212022
20-05-202110-01-20222020212022
25-03-202110-01-20221220212022
18-02-202131-07-2022720212022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202230-03-2022620222022
07-02-202204-08-2022620222022
07-02-202230-03-2022620222022
07-02-202204-08-2022620222022
07-02-202230-03-2022620222022
07-02-202208-08-2022620222022
04-02-202208-08-2022520222022
07-02-202208-08-2022620222022
07-02-202208-08-2022620222022
07-02-202208-08-2022620222022
06-02-202230-03-2022520222022
06-02-202204-08-2022520222022
06-02-202230-03-2022520222022
06-02-202204-08-2022520222022

 

Thanks in advance

Message 1 of 5
992 Views
0
1 ACCEPTED SOLUTION
Russell-PBI
Resolver II
Resolver II

‎02-18-2022 04:44 PM

Hi @Anonymous, does this work?:

PreviousYearApplications = 
VAR StudyYear = MAX(Final_data[Year of StudyStart2])
VAR PrevAppYear = MAX(Final_data[Year of Application2]) - 1
RETURN
CALCULATE(
Final_data[NumberOfApplications]
, ALL(Final_data)
, Final_data[Year of StudyStart2] = StudyYear
, Final_data[Year of Application2] = PrevAppYear
)

Accumulated + Previous Year = Final_data[AccumOfAplications2022] + [PreviousYearApplications]

EDIT: This may be better for when you have multiple years:

PreviousYearApplications = 
VAR StudyYear = MAX(Final_data[Year of StudyStart2])
VAR AppYear = MAX(Final_data[Year of Application2])
RETURN
CALCULATE(
Final_data[NumberOfApplications]
, ALL(Final_data)
, Final_data[Year of StudyStart2] = StudyYear
, Final_data[Year of Application2] < AppYear
)

 

View solution in original post

Message 2 of 5
932 Views
4 REPLIES 4
Russell-PBI
Resolver II
Resolver II

‎02-18-2022 04:44 PM

Hi @Anonymous, does this work?:

PreviousYearApplications = 
VAR StudyYear = MAX(Final_data[Year of StudyStart2])
VAR PrevAppYear = MAX(Final_data[Year of Application2]) - 1
RETURN
CALCULATE(
Final_data[NumberOfApplications]
, ALL(Final_data)
, Final_data[Year of StudyStart2] = StudyYear
, Final_data[Year of Application2] = PrevAppYear
)

Accumulated + Previous Year = Final_data[AccumOfAplications2022] + [PreviousYearApplications]

EDIT: This may be better for when you have multiple years:

PreviousYearApplications = 
VAR StudyYear = MAX(Final_data[Year of StudyStart2])
VAR AppYear = MAX(Final_data[Year of Application2])
RETURN
CALCULATE(
Final_data[NumberOfApplications]
, ALL(Final_data)
, Final_data[Year of StudyStart2] = StudyYear
, Final_data[Year of Application2] < AppYear
)

 

Message 2 of 5
933 Views
Anonymous
Not applicable
In response to Russell-PBI

‎02-21-2022 05:44 AM

Hi, no it returns the following graph

Mercedes_b_0-1645450979915.png

 

 

Message 3 of 5
868 Views
0
Russell-PBI
Resolver II
Resolver II
In response to Anonymous

‎02-21-2022 05:59 AM

Well, that's wacky looking. Maybe put the various columns in a table and see what's happening with the individual sums against DateOfApplication. It worked when I tested it with your sample data, so maybe there's something else going on. The sudden ups and downs on the graph suggest it's adding the previous application count for some days but not others.

Message 4 of 5
864 Views
Anonymous
Not applicable
In response to Russell-PBI

‎02-23-2022 01:32 AM

Thanks alot for the tip, of using a table, to detect what's  happening with the sums!

I have finally succeeded in producing the graph, with carry over from the previous year.

Mercedes_b_0-1645608645706.png

 

Message 5 of 5
798 Views

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All