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: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

How to make Line Chart stop mid-graph for future weeks

‎03-02-2022 08:22 AM

Hi,

 

I've made a line chart, showcasing the accumultion of received applications throughout the weeks of 2022 compared to 2021. 

 

The dataline for 2022, do not have values/data for future weeks and I'd like the line for 2022 to stop mid-graph at the current weeknumber. But, I can't figure out how to.

 

This is my current graph:

Billede1.png

 

Measure for accumulatation current year

CurrentY(Accumulated+PrevYear) =
VAR CurrentWeek_ =
MAX ( 'Calendar'[ISOWeekNum] )
VAR StudyYear = YEAR(TODAY())
VAR AppYear = YEAR(TODAY())
VAR PrevAppYear = StudyYear-1
VAR PrevYFinal =
CALCULATE(
COUNTROWS ( 'Final_data' )
, Final_data[Year of Studystart] = StudyYear
, Final_data[Year of Application] = PrevAppYear
)
Var AccumCY=
CALCULATE (
COUNTROWS ( 'Final_data' ),
'Final_data'[ISOWeekNum] <= CurrentWeek_,
'Final_data'[Year of Studystart] = StudyYear,
'Final_data'[Year of Application]=AppYear
)
Return
AccumCY+PrevYFinal

 

This is a data sample

 

DateOfApplicationStudystartdateReceived Week NumberYear of ApplicationYear of Studystart
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 4
585 Views
0
1 ACCEPTED SOLUTION
littlemojopuppy
Community Champion
Community Champion

‎03-02-2022 09:23 AM

Hi @Anonymous 

 

What you want to do is compare the current (or possibly last) week number available in the data to the week number in the axis - use MAX(WeekNumber).  If the week number in the axis > current/last available in data then BLANK() otherwise show value.

 

Hope this helps!

View solution in original post

Message 2 of 4
544 Views
3 REPLIES 3
Anonymous
Not applicable

‎03-09-2022 01:15 AM

Hi @Anonymous ,

 

How does 'Final_data'[ISOWeekNum] come from?

Does your calendar table have a relationship with the main table?
Your image is not clear enough, I can't see the specific content in Fields.

When publishing, you can enlarge the size of your image.

vstephenmsft_1-1646817264482.png

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Message 3 of 4
507 Views
0
Anonymous
Not applicable
In response to Anonymous

‎03-09-2022 01:29 AM

Hi, 

 

'Final_data'[ISOWeekNum]= if(and(Final_data[Received Week Number]>51,month(Final_data[DateOfApplication])=1),1,Final_data[Received Week Number])
 
to handle whenever week 52 or 53 ends in a new year.
 
I'have not linked the calendar table directedly to the main table, but I'm using the calendar as my date table.
 
I have suceeded in making the line chart stop mid graph implementing the following code
 
CurrentY(Accumulated+PrevYear) =
IF(MAX('Calendar'[ISOWeekNum])>=WEEKNUM(MAX(Final_data[DateOfApplication]),2),Blank(),
VAR CurrentWeek_ =
MAX ( 'Calendar'[ISOWeekNum] )
VAR StudyYear = YEAR(TODAY())
VAR AppYear = YEAR(TODAY())
VAR PrevAppYear = StudyYear-1
VAR PrevYFinal =
CALCULATE(
COUNTROWS ( 'Final_data' )
, Final_data[Year of Studystart] = StudyYear
, Final_data[Year of Application] = PrevAppYear
)
Var AccumCY=
CALCULATE (
COUNTROWS ( 'Final_data' ),
'Final_data'[ISOWeekNum] <= CurrentWeek_,
'Final_data'[Year of Studystart] = StudyYear,
'Final_data'[Year of Application]=AppYear
)
Return
AccumCY+PrevYFinal)
 
Mercedes_b_0-1646817967234.png

 

Message 4 of 4
506 Views
0
littlemojopuppy
Community Champion
Community Champion

‎03-02-2022 09:23 AM

Hi @Anonymous 

 

What you want to do is compare the current (or possibly last) week number available in the data to the week number in the axis - use MAX(WeekNumber).  If the week number in the axis > current/last available in data then BLANK() otherwise show value.

 

Hope this helps!

Message 2 of 4
545 Views

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 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